#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
To do away with the #value:
=IFERROR(your formula),"") |
#3
|
|||
|
|||
Not a solution.
The IFERROR does suppress the #VALUE! return, however it does not return the rest of the calculation.
|
#4
|
||||
|
||||
I did not open the test.file when I replied. May I know your test formula?
|
#5
|
|||
|
|||
=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
|
||||
|
||||
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)) |
#7
|
|||
|
|||
I've never seen this.
|
#8
|
||||
|
||||
Actually you do not need the (B$2:B$10>0) to get the correct result.
|
#9
|
|||
|
|||
(B$2:B$10>0) to get the correct result.
I do not have this code in me spreadsheet. |
#10
|
||||
|
||||
It is there, nested in the formula that I gave you.
|
#11
|
||||
|
||||
Marcia is right; simplest is:
=SUMPRODUCT(A2:A10,B2:B10) |
#12
|
|||
|
|||
It worked and without all of those IF's.
Thanks, David |
Tags |
#value!, adding, if formula |
|
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 |