SOLVED

# Formula returning #value

Hello,

I have an error when trying to calculate a value if certain criteria is met. I'm using Excel 2016 (home/student).

The formula used for the calculation is =IF(SUM(G31:G32)>0,SUM(G31*0.5)+(G32),"")

Any help would be appreciated.

8 Replies

# Re: Formula returning #value

Without opening the file for personal security.

The #VALUE! error in your formula is likely caused by the SUM(G31*0.5) part of the formula. The SUM function takes a range of cells as its argument, but in this case, you are providing it with a single value G31*0.5. This is causing the #VALUE! error.

To fix this error, you can remove the SUM function from this part of the formula and just use G31*0.5 instead.

Here’s the corrected formula:

=IF(SUM(G31:G32)>0,G31*0.5+G32,"")

This formula will check if the sum of cells G31 and G32 is greater than 0 using the SUM(G31:G32)>0 condition.

If this condition is true, it will calculate the value of G31*0.5+G32 and return it as the result. Otherwise, it will return an empty string.

I hope this helps!

best response confirmed by NikolinoDE (Respected Contributor)
Solution

# Re: Formula returning #value

I did open the file and G32 is not a number so you need to address that. Here is an alternative:
=IF(SUM(G31:G32)>0,SUM(G31)*0.5+SUM(G32),"")
in this case SUM is used to ignore blank/text. notice I also pulled *0.5 outside the SUM even though that wasn't the problem it could be

# Re: Formula returning #value

Go to G32 and clear contents. You've got a space in that cell.

# Re: Formula returning #value

Do what Patrick suggested and your existing formula will work.

Btw you have used the SUM function in the wrong way, try it like this and your formula will work. The advantage of using the SUM function as suggested below is, it ignores the text values.

``=IF(SUM(G31:G32)>0,SUM(G31*0.5,G32),"")``

# Re: Formula returning #value

Thanks for the response and the advice. The issue is now resolved.

# Re: Formula returning #value

Thank you for catching that. The issue is now resolved.

# Re: Formula returning #value

Thanks for the response and the advice. The issue is now resolved.

Thanks

# Re: Formula returning #value

Thanks for the response and the advice. The issue is now resolved.

Thanks