Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula chooser?
Hi,
I'm looking for a way to simplify a really loooong IF statement. In one cell, the user chooses a type of calculation. Example: 1-Prior year % to sales (figure already in worksheet) 2-Assigned % (must enter a % in another cell) 3-Fixed Dollar Amt (must enter a $ amt in another cell) In another cell, you get the actual calculation - If they chose 1, multiply sales by prior year % , if they chose 2, multiply sales by the assigned % they entered, if they chose 3, use whatever figure is entered. Right now, I've got 6 choices, and it's all going through a very complicated IF statement such as: =IF(choice=1,do this formula,IF(choice=2, do other formula,IF(choice=3, do yet another formula,....... I'm about to have to add a 7th and 8th choice, and I'm wondering if there is any simpler way to do this. Any ideas? Thanks! |
#2
|
|||
|
|||
One way:
=CHOOSE(A1,formula1, formula2,formula3,...) In article , JKG wrote: Hi, I'm looking for a way to simplify a really loooong IF statement. In one cell, the user chooses a type of calculation. Example: 1-Prior year % to sales (figure already in worksheet) 2-Assigned % (must enter a % in another cell) 3-Fixed Dollar Amt (must enter a $ amt in another cell) In another cell, you get the actual calculation - If they chose 1, multiply sales by prior year % , if they chose 2, multiply sales by the assigned % they entered, if they chose 3, use whatever figure is entered. Right now, I've got 6 choices, and it's all going through a very complicated IF statement such as: =IF(choice=1,do this formula,IF(choice=2, do other formula,IF(choice=3, do yet another formula,....... I'm about to have to add a 7th and 8th choice, and I'm wondering if there is any simpler way to do this. Any ideas? Thanks! |
#3
|
|||
|
|||
you won't be able to add an 8th choice using IF! Look in Help for VLookup. Maybe like this: In cell A1: enter the number 1 Cell A2: 2 Cell A3: 3 In the corresponding rows in the B col, enter the formulas Now you have made the lookup table aka table_array. If the choice they make is in cell A8, the formula will look like this: =VLOOKUP(A8,A1:B3,2,FALSE) -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741 View this thread: http://www.excelforum.com/showthread...hreadid=475052 |
#4
|
|||
|
|||
Son of a gun... I was sure that wouldn't work! I figured if my list in the
CHOOSE statement was a formula, it would return just the text of that formula (i.e. G28*F26), not the actual result of the formula. That's what VLOOKUP did. But you're absolutely right - it returns the results of that formula. It even changes the cell references when you copy it! WOW! Such a simple solution... THANKS! "JE McGimpsey" wrote: One way: =CHOOSE(A1,formula1, formula2,formula3,...) In article , JKG wrote: Hi, I'm looking for a way to simplify a really loooong IF statement. In one cell, the user chooses a type of calculation. Example: 1-Prior year % to sales (figure already in worksheet) 2-Assigned % (must enter a % in another cell) 3-Fixed Dollar Amt (must enter a $ amt in another cell) In another cell, you get the actual calculation - If they chose 1, multiply sales by prior year % , if they chose 2, multiply sales by the assigned % they entered, if they chose 3, use whatever figure is entered. Right now, I've got 6 choices, and it's all going through a very complicated IF statement such as: =IF(choice=1,do this formula,IF(choice=2, do other formula,IF(choice=3, do yet another formula,....... I'm about to have to add a 7th and 8th choice, and I'm wondering if there is any simpler way to do this. Any ideas? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |