(Please see attached sample sheet).
I am used to creating simple formulas if one of 2 conditions is correct, using the “IF” function. I now have a situation like this: I have 4 rows where I can either input a value or a formula will calculate a value (these values will be one of these four – a fraction/a percentage/a specific number/or another number); in the 5th row, I have created an in-cell drop-down box (using data validation) where I will select one of the 4 values in the four rows above. In the 6th row, I want to create a formula which will be different if the choice selected is % (percent) vs. if a number is selected vs. a fraction is selected, etc. (To explain this in simple English, the formula will tell Excel that if cell C8 is selected, multiply that value to that in C5 and subtract the total from C6; if cell C9 is selected, subtract that from C6; if cell C10 is selected, use that value; and if cell C11 is selected, subtract that % from C6.) If there were only 2 choices, the creation of the formula would be easy; unfortunately, there are 4 choices ranging from fractions to percentages so each choice selected will need to have a specific formula applied to it. Since the “IF” function is limited to 2 choices, does anyone know which function in Excel can I use to achieve this? Will it be achieved by using IF multiple times (IF(IF(IF…))) or is there another way?
This one has me stumped! Please see attached sheet – hope that clarifies it!