Adding cells with values generated by IF Formulas

[PJCutts]

Hello

I am trying to add several cells that contain values generated by the following formula.

=IF(Y2="","",IF(Y2<=0,"",IF(Y2>=90,"10",IF(Y2>=80,"9",IF(Y2>=70,"8",IF(Y2>=60,"7",IF(Y2>=50,"6",IF(Y2>=40,"5",IF(Y2>=30,"4",IF(Y2>=20,"3",IF(Y2>=10,"2",IF(Y2<=10,"1"))))))))))))

I keep getting the #VALUE ! in the cell for the results. I believe the problem is when the cell is blank e.g Y2="" but I don't know how to sort out my sum formula which at present is =AA3+AI3+AQ3+AY3+BG3+BO3+BW3+CE3+CM3+CU3+DC3+DK3+DS3+EA3+EI3+EQ3
If I manually take out the cells that have the returned "" data then I can get it to add up.

Thank you so much for your help. I know this makes sense.

 

[StephenCrump]

Re: Question about adding cells with vaules generated by IF Formulas

Welcome to the Forum!

I'd simplify your first formula to: =IF(Y2<=0,"",MIN(10,1+INT(Y2/10)))

Note that this will return values 10, 20, 30 etc, rather than your current formula's strings "10", "20", "30 etc. (When you add these "numbers" Excel will helpfully coerce them into a numerical result, but they won't always behave the way you are expecting).

Given that you're summing values which are neatly spaced 8 columns apart, you could then use:

=SUMPRODUCT(AA3:EQ3,--(MOD(COLUMN(AA3:EQ3)-COLUMN(AA3),8)=0))

which won't error on "" values.

 

[PJCutts]

Re: Question about adding cells with vaules generated by IF Formulas

Welcome to the Forum!

I'd simplify your first formula to: =IF(Y2<=0,"",MIN(10,1+INT(Y2/10)))

Note that this will return values 10, 20, 30 etc, rather than your current formula's strings "10", "20", "30 etc. (When you add these "numbers" Excel will helpfully coerce them into a numerical result, but they won't always behave the way you are expecting).

Given that you're summing values which are neatly spaced 8 columns apart, you could then use:

=SUMPRODUCT(AA3:EQ3,--(MOD(COLUMN(AA3:EQ3)-COLUMN(AA3),8)=0))

which won't error on "" values.

Thank you so much for your reply. Unfortunately, it does not work for me. When I simplify the first formula it works if the cell has data returned from the formula but not if it hasnt, it returns #VALUE ! this is why I used such a convoluted formula in the first instance so that when a cell contained formula but no data it would be blank instead of the error value.

=SUMPRODUCT(AA3:EQ3,--(MOD(COLUMN(AA3:EQ3)-COLUMN(AA3),8)=0)) isn't working for me either, it is returning a value of zero. I appreciate your help or further suggestions.

PJ ​

 

[Joe4]

Re: Question about adding cells with vaules generated by IF Formulas

As was mentioned, you are telling your formula to return Text values and not Numbers (anything enclosed in double-quotes is treated as a string).
So I would change all of those, and return 0 instead of "", i.e.

[COLOR=#333333]=IF(Y2="",0,IF(Y2<=0,0,IF(Y2>=90,10,IF(Y2>=80,9,IF(Y2>=70,8,IF(Y2>=60,7,IF(Y2>=50,6,IF(Y2>=40,5,IF(Y2>=30,4,IF(Y2>=20,3,IF(Y2>=10,2,IF(Y2<=10,1[/COLOR][COLOR=#333333]))))))))))))[/COLOR][COLOR=#333333]
[/COLOR]

or to use a variation of Steven's formula:

[COLOR=#333333]=IF(OR(Y2="",Y2<=0),0,MIN(10,1+INT(Y2/10)))[/COLOR]

If the issue is you do not want to physically see those zeroes, you can just hide them with a Custom Format or Conditional Formatting.
See: https://support.office.com/en-us/ar...o-values-3ec7a433-46b8-4516-8085-a00e9e476b03