Changing formulas based on one cell (period) in worksheet (Goal seek, scenerio?)

[workingmom434]

I need to make the formulas on this worksheet work for all 3 periods. In order to do this the bottom 4 criteria have to change formulas based on cell b10.

In other words, cell

a68 will read (=b22*c73) when cell b10 says 1
a68 will read (=e22*c73) when cell b10 says 2
a68 will reas (=h22*c73) when cell b10 says 3


Any help is greatly appreciated.

[djapigo]

Try this nested IF statement...

=IF(B10=1,B22*C73,IF(B10=2,E22*C73,H22*C73))

[Richard Buttrey]

Hi,

One way would be

Please Login or Register  to view this content.

another way since an Offset formula is volatile would be a simple IF formula

Please Login or Register  to view this content.

[workingmom434]

Richard, The second formula is working except it turns it into a negative number instead of positive. Any ideas. Thank you again.

[workingmom434]

djapigo,

i got close but it worked with period 1 and 3 but #2 wouldn't work.

[workingmom434]

Richard, I accidentally, put a -between the if and the ( so it is fixed. I am so grateful to both of you. Although I don't understand the formula at all, it worked.

[djapigo]

Hmmm , that's odd that 1 and 3 works, but not 2... well, I'm it worked using Richard's formula (although it's not any different from mine... ?)

We showed you a NESTED IF statement...

Regular IF statements need 3 things... condition, value if condition is true, value if condition is false (these 3 items are separated with commas)

It should read... IF x is true THEN y ELSE z

For example, =IF(A1<100,"small number","big number")

A1<100 is a condition which will return TRUE or FALSE
If the condition is TRUE, then it will return a value of "small number"
Else, if the condition is FALSE, then it will return "big number"

A nested IF is taking one of the values and putting another IF statement
For example, =IF(A1<50,"small number",IF(A1<100,"medium number,"big number"))
So the first IF statement says, the condition is A1<50, if that's true then return "small number", else if A1<50 is false, then return the whole 2nd IF statement
Where the second IF statement says the condition is A1<1--, if that's true then return "medium number" else return "big number"

Is that clear enough... I'll leave the OFFSET explanation to Richard... I barely know how to use it...

[Richard Buttrey]

...and the Offset formula works by starting with a given cell (B22 in your case) and working out how many rows or columns to the left/right/above or below the given cell should be used to select the answer cell.

Your required cells are each 3 cells to the right of the preceding cell, so if we use the number in B10, (i.e. 1,2 or 3) we can multiply this number by 3 and deduct the value 3 so that we get either 1x3-3 = 0, 2x3-3=3 or 3x3-3=6. The numbers 0,3 & 6 being the column offset from B22. The Row offset is set to zero.