Microsoft Office Forums Multiple IF formulas that are not nested still returning #VALUE!
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-22-2020, 06:23 PM
 dhouston1000 Windows 10 Office 2010 Novice Join Date: Jan 2020 Posts: 14
Multiple IF formulas that are not nested still returning #VALUE!

I am working on an order form that needs to remain as simple as it currently is. The If statements test to see if a cell is greater than 0. (quantity or how many) If it is then it multiplies that cell by the cell next to it which is the price. Then I add up the cells.

As long as there is a value in the cells I get a proper outcome. If a cell is empty the cell returns #VALUE!.

I looked at putting the logic within each cell but that would require another column and add more complexity to this order form. The people I work with need simple.

Thanks, David
Attached Files
 test.xlsx (16.7 KB, 4 views)
#2
03-22-2020, 07:04 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 261

To do away with the #value:
#3
03-22-2020, 08:39 PM
 dhouston1000 Windows 10 Office 2010 Novice Join Date: Jan 2020 Posts: 14
Not a solution.

The IFERROR does suppress the #VALUE! return, however it does not return the rest of the calculation.
#4
03-22-2020, 08:50 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 261

I did not open the test.file when I replied. May I know your test formula?
#5
03-22-2020, 09:04 PM
 dhouston1000 Windows 10 Office 2010 Novice Join Date: Jan 2020 Posts: 14

=iferror(if(b2>0,b2*a2,"")
+if(b3>0,b3*a3,"")
+if(b4>0,b4*a4,"")
+if(b5>0,b5*a5,"")
+if(b6>0,b6*a6,"")
+if(b7>0,b7*a7,"")
+if(b8>0,b8*a8,"")
+if(b9>0,b9*a9,"")
+if(b10>0,b10*a10,""), "")
#6
03-22-2020, 09:53 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 261

Code:
`=SUMPRODUCT(A\$2:A\$10,B\$2:B\$10*(B\$2:B\$10>0))`
#7
03-22-2020, 10:05 PM
 dhouston1000 Windows 10 Office 2010 Novice Join Date: Jan 2020 Posts: 14

I've never seen this.
#8
03-22-2020, 10:35 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 261

Actually you do not need the (B\$2:B\$10>0) to get the correct result.
#9
03-22-2020, 11:47 PM
 dhouston1000 Windows 10 Office 2010 Novice Join Date: Jan 2020 Posts: 14

(B\$2:B\$10>0) to get the correct result.

I do not have this code in me spreadsheet.
#10
03-23-2020, 12:56 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 261

Quote:
 Originally Posted by dhouston1000 (B\$2:B\$10>0) to get the correct result. I do not have this code in me spreadsheet.
It is there, nested in the formula that I gave you.
#11
03-24-2020, 01:23 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 369

Marcia is right; simplest is:
=SUMPRODUCT(A2:A10,B2:B10)
#12
03-25-2020, 10:53 AM
 dhouston1000 Windows 10 Office 2010 Novice Join Date: Jan 2020 Posts: 14

It worked and without all of those IF's.

Thanks,
David

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post falcios Word 4 08-23-2019 12:13 PM SuziBaz Word VBA 5 04-13-2018 05:11 PM caz46 Excel 5 03-30-2017 03:38 AM stockers Excel 3 03-06-2017 05:05 PM charles_cat Excel 1 01-23-2015 01:30 AM

All times are GMT -7. The time now is 09:48 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top