Formula Question

[bpgolferguy]

Hi, I have this formula in a cell AV5:

=IF(AS5=1, IF(OR(AW5="F",AND(AW5>=1,AW5<=17)),SUM(TourSB2AmMen!AN5:BE5,E5),""), IF(AS5=10, IF(OR(AW5="F",AW5="1*",AW5="2*",AW5="3*",AW5="4*",AW5="5*",AW5="6*",AW5="7*",AW5="8*",AW5="9*",AW5="10*",AW5="11*",AW5="12*",AW5="13*",AW5="14*",AW5="15*",AW5="16*",AW5="17*",AW5="18*"),SUM(TourSB2AmMen!BG5:BX5,E5),"")))

Yes, I know, but it really does serve a purpose and it works!...except for one thing. I would like cell AV5 to show the current value of cell E5 if none of those conditions in the formula exist. I'm sure it's just as simple as putting something at the very beginning, but I had help on this and I don't know even where to begin. Thanks in advance!

 

[texasalynn]

change the last part

"" to E5

 

[bpgolferguy]

Do I need to leave the "" around it? I've tried it both ways, with and without, and it's not working....

 

[bpgolferguy]

I'm thinking do I maybe need to put a couple of ) in front of it at the end? If I stick it inside those three is it still being applied to the original argument at the very beginning of the formula?

 

[T. Valko]

Do I need to leave the "" around it? I've tried it both ways, with and without, and it's not working....

I won't tell you that you'd be better off using a range of cells to hold all those variables...

Try it like this...

=IF(AND(AS5=1,OR(AW5="F",AND(AW5>=1,AW5<=17))),SUM(TourSB2AmMen!AN5:BE5,E5),IF(AND(AS5=10,OR(AW5={"F","1*","2*","3*","4*","5*","6*","7*","8*","9*","10*","11*","12*","13*","14*","15*","16*","17*","18*"})),SUM(TourSB2AmMen!BG5:BX5,E5),E5))

 

[bpgolferguy]

I won't tell you that you'd be better off using a range of cells to hold all those variables...

Try it like this...

=IF(AND(AS5=1,OR(AW5="F",AND(AW5>=1,AW5<=17))),SUM(TourSB2AmMen!AN5:BE5,E5),IF(AND(AS5=10,OR(AW5={"F","1*","2*","3*","4*","5*","6*","7*","8*","9*","10*","11*","12*","13*","14*","15*","16*","17*","18*"})),SUM(TourSB2AmMen!BG5:BX5,E5),E5))

Hey hey! That worked! And it solved the other problem I just posted. You're the man! Thanks!

 

[T. Valko]

Hey hey! That worked! And it solved the other problem I just posted. You're the man! Thanks!

You're welcome. Thanks for the feedback!