Solved Nesting or within if function

March 22, 2013 at 08:04:39
Specs: Windows Vista
I have two pages to my spreadsheet (criteria and quote). I need a formula in quote F12 saying that if there is anything in criteria C10 or D10 or in quote F11 then (Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),"" (that last formula is correct). This works fine when there are figures in all boxes but when F11 is blank I get #DIV/0! error code. I think I need to add the ‘or’ function but don’t know how to incorporate it. This is my full formula =IF(Criteria!$C10+Criteria!$D10+F$11>0,(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),"")

Any sugestions would be appreciated.


See More: Nesting or within if function

Report •

✔ Best Answer
March 24, 2013 at 15:03:51
Try this one:

=IF(AND(F$11<>"",Criteria!C10<>"",Criteria!D10<>""),(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),"")

It will test if F11, Criteria!C10 & Criteria!D10 are blank
If any of the three cells are blank, it will return null.
You will need data in all three cells to get a result.

MIKE

http://www.skeptic.com/



#1
March 22, 2013 at 08:39:05
Not sure what your doing, but the simplest way is to check F$11 first:

=IF(F$11="","",IF(Criteria!$C10+Criteria!$D10+F$11>0,(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),""))

If F11 is blank, do nothing.

MIKE

http://www.skeptic.com/


Report •

#2
March 22, 2013 at 09:51:34
I am laying out a spreadsheet to work out prices for items we manufacture. The customer could ask for, say 10off and 20off prices. Our machines have to be set up (the cost of this would be spread across the 10 or 20 pieces) and then there is the time the machine takes to punch each item. Hence ‘criteria’ C10 is the set up price, ‘criteria’ D10:J10 is the punch price and ‘quote’ D11:G11 are the numer of items required so if there is a figure in any one of those cells I want a calculation in ‘quote’D12 (10off) and E12 (20off). That is easy using ‘if’ and there are figures in all the cells but when, in this case, I am not using all the columns and ‘quote’ F11 is blank then I get the error message.

I hope that all makes some sort of sense but I am unable to take a screenshot as my computer locks out.


Report •

#3
March 22, 2013 at 10:10:29
Try this,
this formula might be better suited to your needs:

=IF(Criteria!$C10+Criteria!$D10+F$11>0,IF(F$11<>"",(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),Criteria!$C10+SUM(Criteria!$D10:$J10)))

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 22, 2013 at 13:01:43
I could be wrong, but I think Mike's formula can be shortened a bit:

Note: Mike did all the hard work, I just played with the syntax.

=IF(SUM(Criteria!$C10:$D10,F$11)>0,IF(F$11<>"",
SUM(Criteria!$C10/F$11,Criteria!$D10:$J10),
SUM(Criteria!$C10:$J10)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
March 24, 2013 at 11:21:03
Thanks for the suggestions but they do not work. To make it simpler, if there is a figure in cell A1 or B1 or C1 then 'yes' if there is something in A1 or B1 but not in C1 then 'no'

Report •

#6
March 24, 2013 at 11:43:14
if there is a figure in cell A1 or B1 or C1 then 'yes' if there is something in A1 or B1 but not in C1 then 'no'

Does this not then make C1 the key?
It reduces down to =IF(C1<>"",YES,NO)
It does not really matter if A1 or B1 has data or not.

MIKE

http://www.skeptic.com/


Report •

#7
March 24, 2013 at 12:33:42
What if all three cells are empty? Is that possible?

If so, then just checking C1 isn't enough. The "no" required "something in A1 or B1" along with nothing in C1.

All three cells being empty doesn't fit that criteria, but I don't know if that will ever happen. Only the OP can tell us that.

P.S. I have deleted the other 'continued' thread since this one seems to be working fine. If you (OP) continue to have problems posting, let me know and I'll pass it up the chain.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 24, 2013 at 12:41:56
If all three cells are empty, I get FALSE as the answer.

The point I was trying to make, was in his formula the F11 cell is the key.

In his formula, if F11 is blank, you get the DIV/0 error,
To correct this error you can either make sure F11 is never blank
or you can skip over the division with F11 section and continue on with the calculations. Which is what both our formulas do.

But since his only reply was:
Thanks for the suggestions but they do not work.
with no indication of what is happening not much else we can do.

MIKE

http://www.skeptic.com/


Report •

#9
March 24, 2013 at 13:56:21
Hey, I'm on your side. As far as I can tell, your original suggestion fit the requirements laid out in response #2.

AFAICT, his A1, B1, C1 "simplification" is far to simple to meet his original needs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 24, 2013 at 14:19:59
Bananaboat's spreadsheet
Hopefully I have managed to upload a screenshot of the spreadsheet I am working on which should be more explanatory.

I apologise but the reason the suggested formula did not work was because it had a number when it needed to be empty when there was nothing in F11.


Report •

#11
March 24, 2013 at 14:42:33
because it had a number when it needed to be empty when there was nothing in F11.

Are you saying you need to have no calculations done if F11 is blank?

If so, then my first formula:

=IF(F$11="","",IF(Criteria!$C10+Criteria!$D10+F$11>0,(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),""))

Should work.

Still trying to figure out what's going on in your spreadsheet.

MIKE

http://www.skeptic.com/


Report •

#12
March 24, 2013 at 15:03:51
✔ Best Answer
Try this one:

=IF(AND(F$11<>"",Criteria!C10<>"",Criteria!D10<>""),(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),"")

It will test if F11, Criteria!C10 & Criteria!D10 are blank
If any of the three cells are blank, it will return null.
You will need data in all three cells to get a result.

MIKE

http://www.skeptic.com/


Report •

#13
March 24, 2013 at 15:17:23
I do apologise mmcconaghy, this formula does work. I don't know what I did first time but it is doing what I want. Thank you.

Can you explain what the first if does


Report •

#14
March 24, 2013 at 15:21:14
Which formula are you referring to?

MIKE

http://www.skeptic.com/


Report •

#15
March 24, 2013 at 15:26:02
In your reply '11 you give me the formula
=IF(F$11="","",IF(Criteria!$C10+Criteria!$D10+F$11>0,(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),""))

Does the first 'if' say if there is nothing in F11 then return blank? And does that override the following 'if'' bit of the formula?


Report •

#16
March 24, 2013 at 16:26:15
By way of explanation:

The structure of an IF statement is:

IF( condition to test , [value_if_true] , [value_if_false] )

Note that each section is delimited by a comma.
And each Value_If section can itself be another IF statement,
which are referred to as Nested IF's

So in the formula I posted the first section or condition to test is:

=IF ( F$11 = "" , or in words: Test if F11 is blank,

If this returns TRUE, then the second section value_if_true is

"" which means an empty cell or do nothing and processing stops.

If this returns FALSE, then the third section value_if_false is:

IF(Criteria!$C10+Criteria!$D10+F$11>0,(Criteria!$C10/F$11)+SUM(Criteria!$D10:$J10),""))

Which is simply your original =IF() formula

Does that help?

MIKE

http://www.skeptic.com/


Report •

#17
March 24, 2013 at 16:48:15
Yes, thank you, that makes sense.

Thank you for all your help, it is much appreciated.


Report •

Ask Question