spreadsheet wizards please help

[poolman]

In fairness to the person requesting the information, I should point out that this is not an officially assigned task nor part of my 'job description' but i develop for myself, and share with others, ways to get things done more efficiently and in formats that can be easily seen/communicated.

See post: http://www.tsptalk.com/mb/showthread.php?p=253416#post253416

However, i regularly make myself available and contribute my skills/knowledge/desire to filling gaps when they occur, in many program areas and across both software and personal interaction platforms. That comes from my previous non-government employment where the job must get done at all costs, now. Although I've found that is often not well received or understood in gubmint circles.

I have developed good working relationships with both peers and upper level management (if they don't find you handsome at least they may find you handy).

While that won't get me past HR screening programs in my career progression, when I do arrive at an interview many of these folks will be sitting on that board and have a personal experience to relate to when
i tell them i am the person for the job.

gotta have a hobby.

That's great burrocrat,

It was totally different environment for me. It was not a Hobby. I was the Senior Programmer Analyst Canada Dry Co. for nine years. Worked directly for the CFO and President of Company. Constant Grind. $$$ were awesome stress wasn't. Everyone was replaceable if you know what I mean. Oh well less stressful day's other than this Market we are in.

 

[peterson82]

you can create your own function, like LARGEIF()
then in the programming side, just write out all the if-thens

The conversion is simple
=IF(logic_test1,[true1],if(logic_test2,[true2],if(...

function LARGEIF(input as double) as double
if logic_test1 then
LARGEIF = [true1]
elseif logic_test2 then
LARGEIF = [true2]
elseif
.
.
.

 

[burrocrat]

you can create your own function, like LARGEIF()
then in the programming side, just write out all the if-thens

The conversion is simple
=IF(logic_test1,[true1],if(logic_test2,[true2],if(...

function LARGEIF(input as double) as double
if logic_test1 then
LARGEIF = [true1]
elseif logic_test2 then
LARGEIF = [true2]
elseif
.
.
.

thanks for the reply peterson82,

this looks like a way to use the same arguements i had in mind without nesting.

I'm not sure if i understand how to exactly write it?

=IF(logic_test1,[true1],if(logic_test2,[true2],if(...

would be: =IF("meeting_1",[(D2*E2*sheet!C2)+(D2*F2*Sheet2!C2)+(H2*2*Sheet2!C5)],IF("meeting_2",(.....

with the 3 calculations in brackets [ ... ] being lodging, per diem, and mileage for each meeting location?

This uses same IF logic but not nested? did i get that right?

thanks for the clue.

 

[burrocrat]

thanks to all of you for your quick and timely responses. i have lots to work with now.

i didn't even get a chance to play with it today, full schedule of clients and calls wall to wall, but did speak with and forward the links you provided to the other party.

i will play with it this weekend and see what comes out. i think i can use these tips to get an appropriate answer and shine it up later for wider use.

thanks again to all the great folks on this board!

 

[burrocrat]

done, shut 'er down.

that LOOKUP thing is the rahm!

it took less time to finish the spreadsheet using HLOOKUP (including cleaning up the data table, modifying and adding columns to a blank front page, write the basic multiplication and addition formulas, inputting and testing a full range of data, formatting display of cells, and buttoning things down, than it did to try to count (parenthesis)))))))) for just one IF/THEN formula that didn't actually work as intended.

Grand prize goes to Bluehenge for the LOOKUP lead in the second link of that post.

My gratitude goes to all of you who set me on the right path and gave me enough possiblities to think about so that a solution could emerge.

Spreadsheet will be in my supervisor's inbox before first thing Monday morning. Maybe some good will come of this.

It isn't fancy and could use some gussying up, but it is exactly what was called for, amazing what can be done with only 52kb.

Thanks again to all.

 

[burrocrat]

this is what i came up with.

View attachment 8110

changed the names to protect the innocent.

suggestions are welcome, it doesn't need to be submitted until monday am.

 

[burrocrat]

well i slept on it and got a few new ideas.

i'm going to add a column to indicate if a government-owned vehicle is being used and an IF statement in the mileage cost column to drop out mileage expenses where appropriate.

also, going to sort the counties by district instead of alphabetically to make it easier to examine regional scenarios and provide utility to district level managers for smaller training sessions in the future (original problem was just to get the cost of one state-wide meeting in various locations).

maybe will try to add another column to estimate travel time based on mileage in order to ditch the crazy color scheme that came with the original data table.

 

[Bluehenge]

Grand prize goes to Bluehenge for the LOOKUP lead in the second link of that post.

Burrocrat

Thank you very much for the recognition Burrocrat.

I am glad it has worked out for you and hope it works out for your supervisor. All depends on those "beancounters" now!

Thanks for sharing the spreadsheet.

Bluehenge
P.S. You chose an appropriate name (based on this thread).

 

[burrocrat]

the tips you all provided and i forwarded on friday were enough to set the state executive on the right path, by monday morning we had both developed similar solutions. minor differences using VLOOKUP vs. HLOOKUP, using color coding vs. formulas for travel time estimation, and visually different organization and different way of writing formulas but returning same numbers. The gov vehicle travel thing made it into the final copy too.

It's neat how different folks arrive at the same solution in different ways.

Thanks for the tips so i could strengthen a good working relationship and help provide solutions. Once the effort pays off in the future I may be in a position to buy the drinks, appetizers, shirley temples, whatever at the next proposed TSPtalk convention in WDC or Vegas, although i'd prefer vegas.

It's up to the bean counters now to decide if we do split trainings or a whole state meeting. I'll let you know the desicion as i find out, maybe common sense will win the day, but this is a burrocracy after all. cross your fingers.