Forum Discussion
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.
- 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
8 Replies
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),"")- Thanks for the response and the advice. The issue is now resolved.
- Go to G32 and clear contents. You've got a space in that cell.
- Thank you for catching that. The issue is now resolved.
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!
- Thanks for the response and the advice. The issue is now resolved.
Thanks - 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- Thanks for the response and the advice. The issue is now resolved.
Thanks
