Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-22-2020, 06:23 PM
dhouston1000 dhouston1000 is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 10 Multiple IF formulas that are not nested still returning #VALUE! Office 2010
Novice
Multiple IF formulas that are not nested still returning #VALUE!
 
Join Date: Jan 2020
Posts: 14
dhouston1000 is on a distinguished road
Default 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
File Type: xlsx test.xlsx (16.7 KB, 4 views)
Reply With Quote
  #2  
Old 03-22-2020, 07:04 PM
Marcia's Avatar
Marcia Marcia is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 7 32bit Multiple IF formulas that are not nested still returning #VALUE! Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 261
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

To do away with the #value:
=IFERROR(your formula),"")
Reply With Quote
  #3  
Old 03-22-2020, 08:39 PM
dhouston1000 dhouston1000 is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 10 Multiple IF formulas that are not nested still returning #VALUE! Office 2010
Novice
Multiple IF formulas that are not nested still returning #VALUE!
 
Join Date: Jan 2020
Posts: 14
dhouston1000 is on a distinguished road
Default Not a solution.

The IFERROR does suppress the #VALUE! return, however it does not return the rest of the calculation.
Reply With Quote
  #4  
Old 03-22-2020, 08:50 PM
Marcia's Avatar
Marcia Marcia is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 7 32bit Multiple IF formulas that are not nested still returning #VALUE! Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 261
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

I did not open the test.file when I replied. May I know your test formula?
Reply With Quote
  #5  
Old 03-22-2020, 09:04 PM
dhouston1000 dhouston1000 is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 10 Multiple IF formulas that are not nested still returning #VALUE! Office 2010
Novice
Multiple IF formulas that are not nested still returning #VALUE!
 
Join Date: Jan 2020
Posts: 14
dhouston1000 is on a distinguished road
Default

=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,""), "")
Reply With Quote
  #6  
Old 03-22-2020, 09:53 PM
Marcia's Avatar
Marcia Marcia is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 7 32bit Multiple IF formulas that are not nested still returning #VALUE! Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 261
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

I am not sure if I understood what you wanted but how about this
Code:
=SUMPRODUCT(A$2:A$10,B$2:B$10*(B$2:B$10>0))
Reply With Quote
  #7  
Old 03-22-2020, 10:05 PM
dhouston1000 dhouston1000 is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 10 Multiple IF formulas that are not nested still returning #VALUE! Office 2010
Novice
Multiple IF formulas that are not nested still returning #VALUE!
 
Join Date: Jan 2020
Posts: 14
dhouston1000 is on a distinguished road
Default

I've never seen this.
Reply With Quote
  #8  
Old 03-22-2020, 10:35 PM
Marcia's Avatar
Marcia Marcia is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 7 32bit Multiple IF formulas that are not nested still returning #VALUE! Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 261
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Actually you do not need the (B$2:B$10>0) to get the correct result.
Reply With Quote
  #9  
Old 03-22-2020, 11:47 PM
dhouston1000 dhouston1000 is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 10 Multiple IF formulas that are not nested still returning #VALUE! Office 2010
Novice
Multiple IF formulas that are not nested still returning #VALUE!
 
Join Date: Jan 2020
Posts: 14
dhouston1000 is on a distinguished road
Default

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



I do not have this code in me spreadsheet.
Reply With Quote
  #10  
Old 03-23-2020, 12:56 AM
Marcia's Avatar
Marcia Marcia is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 7 32bit Multiple IF formulas that are not nested still returning #VALUE! Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 261
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by dhouston1000 View Post
(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.
Reply With Quote
  #11  
Old 03-24-2020, 01:23 AM
p45cal p45cal is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 10 Multiple IF formulas that are not nested still returning #VALUE! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 369
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Marcia is right; simplest is:
=SUMPRODUCT(A2:A10,B2:B10)
Reply With Quote
  #12  
Old 03-25-2020, 10:53 AM
dhouston1000 dhouston1000 is offline Multiple IF formulas that are not nested still returning #VALUE! Windows 10 Multiple IF formulas that are not nested still returning #VALUE! Office 2010
Novice
Multiple IF formulas that are not nested still returning #VALUE!
 
Join Date: Jan 2020
Posts: 14
dhouston1000 is on a distinguished road
Default

It worked and without all of those IF's.


Thanks,
David
Reply With Quote
Reply

Tags
#value!, adding, if formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word moves up multiple pages after returning to document falcios Word 4 08-23-2019 12:13 PM
VBA for ContentControl returning multiple lines of text SuziBaz Word VBA 5 04-13-2018 05:11 PM
Adding IF in nested formulas caz46 Excel 5 03-30-2017 03:38 AM
Returning value based on multiple crtiteria stockers Excel 3 03-06-2017 05:05 PM
copying nested if over multiple rows where one value stays fixed charles_cat Excel 1 01-23-2015 01:30 AM


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft