spreadsheet wizards please help

[burrocrat]

I have been asked to help a high level supervisor develop a spreadsheet and need some help with a complex (at least to me) IF "xxxx" THEN formula. I will describe specifics and context in a later post, but here is the basic problem as best I understand it right now:

MS Excel seems to allow only about 7 possible IF arguments but I have 11 options I need to consider, each if true leading to a series of calulations to arrive at a best cost solution.

How can I get around this or is there a better formula to use?

Can I write it so that if none of the first 6 are true then it uses the last arguement to proceed to another cell containing the remaining variables?

My supervisor at the state level needs it completed and in the hands of their supervisor in WDC by Wednesday to make the case or it is too late, resulting in more expense for the agency and less opportunity for whole group brainstorming and teambuilding (the whole point of the exercise in the first place).

Any Ideas?

Thanks in advance.

[WorkFE]

Sounds like you have a tuff one there. Never had to write "if then" formulas. I have on occasion found EXCEL forums reliable and quick. They tend to be full of IT folks.
As a challenge it sounds like it would be fun to work on but under those time constraints and it is for the Job you need real help.

GL

[burrocrat]

Background: state level executive needs to bring about 50 county level executives from different locations together for a management training meeting. Purpose is to get input from the field on how to better serve the needs of clients/customers, streamline operations and improve efficiency, and disseminate procedure and policy; but probably more importantly to build and reinforce working relationships, share 'tips and tricks', and get everyone on the same page pulling for the team, in theory this flows down to local level employees and results in better performance and service.

Problem is the travel cost to do this is above the state level authority and requires approval from Washington DC, which is unlikely to happen unless the beancounters can see the value penciled out. Obvious workaround is to do a series of smaller regional sessions within the local budget approval authority. But this almost certainly will cost more in total, and will definately degrade the opportunity for gathering wholistic input and team building.

Details to follow.

[burrocrat]

Here is what I have to work with:

A table with 11 possible meeting locations and their corresponding lodging and meals/expenses rates, as well as official mileage from each of the 50 'traveling from' locations to each of the 'traveling to' locations.

Color coding travel into categories of a) less than 1 hour, b) 1-2 hours, c) 2-4 hours, and d) 4+ hours reveals the obivious central location (avoid red and go for green - simple right?) requiring the least amount of mileage for the most amount of people and also happens to have lower lodging and expense rates than the others.

But most beancounters don't think graphically and doesn't result in hard number yes do it answer (in my experience and no offense intended - we all have our strengths and weaknesses).

While not simple, a formula that pulls populated data from above mentioned table can be written to account for number of people from each location, mileage x rate, number of lodging nights x rate, M&IE expenses x rate for a total location cost, and then each location totaled for state cost. Also could be used to compare with different multiple smaller regional meeting scenarios. This $ comparison is necessary for higher level approval. It's not that the alternative gets dissaproved so much as there is never an official response to the request so nothing gets done, gotta love the burrocracy.

[phil]

You probably don't need just a spreadsheet, but an operations management program, if you're trying to maximize results. A couple of programs come to mind.

[burrocrat]

maybe i'm approaching this from the wrong direction but needed to think out loud and hoping someone can help shed some light. Are there other ways to look at this or describe the problem better? i'm a little chicken to just let this fly in an unknown forum before i understand just what i'm asking for and trust most of you on this board share a common goal of stewarding taxpayer resources and serving our country, whatever form that takes for each of us.

Next stop is microsoft online help search. wish me luck.

suggestions?

can anyone recommend a good msexcel or software forum?

[burrocrat]

You probably don't need just a spreadsheet, but an operations management program, if you're trying to maximize results. A couple of programs come to mind.

you're probably right but we don't have that available, will not be spending money on anything new, and couldn't get up to speed on it in time anyway, this is a side project to help a friend, not part of my usual official duties.

I would still be interested to hear your software recommendations, maybe there is a free trial period and i could use it at home this weekend.

thanks.

[WorkFE]

Just ask or just answer, something like that. I never worry what they'll think of me since we don't know each other. I do my best not to sound to computer dumb.:nuts: I do know that pride kicks in, yes even in computer peeps, and they figure it out.

[Bluehenge]

See if this link can help you ...

http://www.cpearson.com/excel/nested.htm

or, if you want to use VLOOKUP check out the best answer at this link...

http://answers.yahoo.com/question/index?qid=20080104045041AA0M83o

Bluehenge

[burrocrat]

some basic research indicates the IF function can contain up to 64 arguements, which should work for me so long as the true values in "xxxx" can be a formula referencing operations on cells called from another sheet/table with the mileage/expense numbers and not just simple text. I only have the raw data not the actual formulas that 'aren't working' so i don't have any clues as to what has been tried or may need fixing.

i'll give that a try tomorrow with the spreadsheet at work, i just got a phone call late in the day with a call for help followed by an email attachment, but had a full schedule in front of me.

it also appears the SUMIF or LOOKUP functions could be used as well if they allow calculations on not just "text" but putting a formula in the quotations and then return result if true.

[burrocrat]

See if this link can help you ...

http://www.cpearson.com/excel/nested.htm

or, if you want to use VLOOKUP check out the best answer at this link...

http://answers.yahoo.com/question/index?qid=20080104045041AA0M83o

Bluehenge

thanks Bluehenge, i will follow up on the links there.

[burrocrat]

wow, thanks Bluehenge, a wealth of information there.

from the first link: the 7 nested operations things is true then, but without the benefit of seeing the actual formula the other person has written that isn't working i can't confirm the solution just yet. it seems you can use up to 64 if-then tests, but each test can only contain seven internal operations. i think the problem is nesting 'if' statements. it seems to me i only need 11 separate and consecutive 'if then' statements should be ok, and each 'if then' contains simple - but long - multiplication and addition operations. as long as you don't nest the 'if's. maybe the problem with the prior approach is a 'everybody in the pool now' vs. 'linear thinking one at a time' thing.

from second link: i need to learn how to use the VLOOKUP function instead to accomplish the same task in a simpler form, and i've already been given the necessary table/array that formula wants.

good leads there, thanks for the response. i'll give it a shot with the actual spreadsheet and data tomorrow.

thanks again!

[WorkFE]

Sorry it took awhile to get the link, had to get it from someone else. Warning I have not verified it myself but I consider my sis a pretty good source:D

http://www.techonthenet.com/excel/formulas/case.php

[poolman]

wow, thanks Bluehenge, a wealth of information there.

from the first link: the 7 nested operations things is true then, but without the benefit of seeing the actual formula the other person has written that isn't working i can't confirm the solution just yet. it seems you can use up to 64 if-then tests, but each test can only contain seven internal operations. i think the problem is nesting 'if' statements. it seems to me i only need 11 separate and consecutive 'if then' statements should be ok, and each 'if then' contains simple - but long - multiplication and addition operations. as long as you don't nest the 'if's. maybe the problem with the prior approach is a 'everybody in the pool now' vs. 'linear thinking one at a time' thing.

from second link: i need to learn how to use the VLOOKUP function instead to accomplish the same task in a simpler form, and i've already been given the necessary table/array that formula wants.

good leads there, thanks for the response. i'll give it a shot with the actual spreadsheet and data tomorrow.

thanks again!

Hello Burrocrat,

I understand what you are trying to do If / Then type calculations. This type of programming can get pretty complex. I would have written this type of program using rpgII and ocl. There are no limitations on how many variables are involved. Programs of this nature can have 50 pages or more of source code before the program is even compiled.

Unfortunately the problem becomes that some senior executive (who has little to no programming experience) wants a report that can be manipulated with massive variable input. He is passing this off onto you. Honestly this is the type of programming that should have been handed to a IS Manager and with more time than has been given. Who is going to run this program and who is going to enter all the data ? Next you will be attempting to train said individual and he will say the Hell with this You do it. Honestly if your in over your head with this. Read what I have written to you to him.

You have some programming experience with the programs you have mentioned but you don't know if what he wants can be done with what you know or have to use. It does not sound like the kind of program that can handle massive variable input with the limitations that you have mentioned using a spreadsheet program.

This can be done on a midrange machine ( Ex. system36 / AS400 ) using rpg and ocl. This is not the type of Programming that you pop out of a Laptop or desktop.

[burrocrat]

Sorry it took awhile to get the link, had to get it from someone else. Warning I have not verified it myself but I consider my sis a pretty good source:D

http://www.techonthenet.com/excel/formulas/case.php

thanks for the tip WorkFE. I don't know how to incorporate Visual Basic commands in Excel, but given the techtip it is possible so can be learned. I will play with several options today as time allows.

I know from several leads i've gotten so far that what i want to do can be done, it may be a little clunky the first time off but will suffice. can be tightened up later to minimize user input and look prettier.

thanks.

[CapeChem]

Try breaking your if/thens into different cells....you can do this prior to writing a gargantuan if/then in one cell just to make sure your individual if/thens function properly......its easy to get lost in the logic if your trying to put everything in one cell right from the start.

[burrocrat]

Hello Burrocrat,

I understand what you are trying to do If / Then type calculations. This type of programming can get pretty complex. I would have written this type of program using rpgII and ocl. There are no limitations on how many variables are involved. Programs of this nature can have 50 pages or more of source code before the program is even compiled.

This can be done on a midrange machine ( Ex. system36 / AS400 ) using rpg and ocl. This is not the type of Programming that you pop out of a Laptop or desktop.


thanks poolman,

i don't recognize rpgII and ocl, but i do have access to and experience with S36 / AS400. I often modify 'canned' queries to provide reports to help accomplish tasks, usually the big problem is identifying which SCOAP file(s) to pull data from. that and finding a block of time to switch the brain to 'greenscreen' mode and brush up on language skills. that is a little unpractical here given my normal workload and time constraints.

i believe all your responses have given me clues to the logic involved in pulling this off, it can be done, i intend to find an expedient - but probably crude and data entry intensive - solution to the specific problem. once that is done it can be cleaned up to apply to similar situations involving miminal user input. just select 'to' location and number of employees 'from' each satellite location. program can calculate number of lodging nights and per diem based on distance. of course lock cells containing formulas / limit user input to avoid code corruption.

[burrocrat]

Try breaking your if/thens into different cells....you can do this prior to writing a gargantuan if/then in one cell just to make sure your individual if/thens function properly......its easy to get lost in the logic if your trying to put everything in one cell right from the start.

yes, i think you're right. much simpler on the front end, if not the 'magical' enter one location and viola! out comes the perfect answer result supervisor is looking for. the break it down into smaller blocks of known workable formulas is within my skills at this point in time.

thank you.

[poolman]

thanks poolman,

i don't recognize rpgII and ocl, but i do have access to and experience with S36 / AS400. I often modify 'canned' queries to provide reports to help accomplish tasks, usually the big problem is identifying which SCOAP file(s) to pull data from. that and finding a block of time to switch the brain to 'greenscreen' mode and brush up on language skills. that is a little unpractical here given my normal workload and time constraints.

i believe all your responses have given me clues to the logic involved in pulling this off, it can be done, i intend to find an expedient - but probably crude and data entry intensive - solution to the specific problem. once that is done it can be cleaned up to apply to similar situations involving minimal user input. just select 'to' location and number of employees 'from' each satellite location. program can calculate number of lodging nights and per diem based on distance. of course lock cells containing formulas / limit user input to avoid code corruption.

Awesome Burrocrat !

And Hint's where what I was trying to send you. Sound's to me like you can do it and you have the level of experience to get it done. Once you know what you need and how it will work in your head and that it is doable you already feel better. Good Luck and let us know how it goes next Wednesday. :)

[burrocrat]

Unfortunately the problem becomes that some senior executive (who has little to no programming experience) wants a report that can be manipulated with massive variable input. He is passing this off onto you. Honestly this is the type of programming that should have been handed to a IS Manager and with more time than has been given. Who is going to run this program and who is going to enter all the data ? Next you will be attempting to train said individual and he will say the Hell with this You do it. Honestly if your in over your head with this. Read what I have written to you to him.

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 (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 recieved 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.

[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 (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.

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



:D 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.