Formula help (1 Viewer)

[BobNTN]

In F13, I have this --> =IF(ISBLANK(E13),"",IF(SUM(E13-D13)>=0,ROUNDUP(SUM(E13-D13)/2,0)+D13,ROUNDDOWN(SUM(E13-D13)/2,0)+D13))

In K13 I have this --> =IF(ISBLANK(E13),"0",MEDIAN(4,-4,E13-D13))

Lastly in N13 I have this --> =IF(AND(ISBLANK(E13),ISBLANK(E14)),"",K13+L13+M13+K14+L14+M14)

My question
In K13 formula if I use "" instead of "0", I get #Value in N13 if any of the other cells are blank

If I use "0", I don't get the value but I have 0's in N13.

I would like blank cells until all conditional cells have data. Is there another option other than "" and "0" ?

Thanks in advance

 

[Peter Reid]

Are the 'conditional cells' in "I would like blank cells until all conditional cells have data" E13 and E14?

If so, I would use "" instead of "0" and change the tests for ISBLANK to ISNUMBER (and swap the true and false values around), ie

In F13, --> =IF(ISNUMBER(E13),IF(SUM(E13-D13)>=0,ROUNDUP(SUM(E13-D13)/2,0)+D13,ROUNDDOWN(SUM(E13-D13)/2,0)+D13),"")

In K13, --> =IF(ISNUMBER(E13),MEDIAN(4,-4,E13-D13),"")

In N13, --> =IF(AND(ISNUMBER(E13),ISNUMBER(E14)),K13+L13+M13+ K14+L14+M14,"")​

 

[BobNTN]

Thanks. Makes sense.
I will give that a try and let you know how it does.

 

[BobNTN]

First two work fine but the third (N13) gives a #Value until all cells are completed.
And yes I put in the new formula in 13 and 14 cells.

Would like to see results in N13 as they are completed.

 

[BobNTN]

Here is a simplified version of what I am trying to do.
Only rows 9 and 10 rather than 13 and 14

 

[Peter Reid]

L9 and M9 are not numbers (because G9 and H9/I9 are blank, respectively) in your attachment, maybe you should test for isnumber on all of the cells you wish to add (or use in calculations)?

 

[BobNTN]

It's because of the "" in the formulas, correct ? So I don't know how to get around having a blank cell and having it in a calculation.

 

[BobNTN]

Now have ISNUMBER in all formulas. Will not calculate on any cell that is resulting in "" so I changed "" to 0 and they all work without #VALUE. Only thing is having zeros in all formula cells until data is input.
Shame there isn't an alternative to "" that can be calculated.

Also, can you have 3 IFs in a formula with 3 different TRUES ?

In other words, If cell c9 has data do this calc, but if cells c9 and e9 have data do this and if c9 e9 and h9 all have data do this, else ""

 

[Brianwarnock]

Instead of

K13+L13+M13+ K14+L14+M14

use
Sum(K13,L13,M13,K14,L14,M14)

Brian

 

[BobNTN]

Thanks Brian.
That works on the N cell formula.

How about the 3 if conditions ? Is that possible ?

What I need is:
If e9 has a number, then e9-d9. If e9 AND G9 both have a number then add e9, G9 minus D9, F9. If e9,G9,I9 all have data, then add e9,G9,I9 minus D9,F9,H9. ELSE "".

Is that possible to do in one formula ?

 

[Brianwarnock]

You would have to do the test in the opposite order to that written, something like

If(and(isnumber(e9),isnumber(g9),isnumber(i9)),true result,and(test the two),true,isnumber(e9),true,"")

Brian

 

[BobNTN]

Ok, tried

=IF(AND(ISNUMBER(I11),ISNUMBER(G11),ISNUMBER(E11)),SUM((E11+G11+I11)-(D11+F11+H11)),AND(ISNUMBER(E12),ISNUMBER(G12)),SUM((E12+G12)-(D12+F12)),AND(ISNUMBER(E12),SUM(E12-D12)),"")

Says I've entered too many arguments for this function.

tried each of the 3 parts separately and they work by themselves

 

[Brianwarnock]

oops left the second and third if out.

=IF(AND(ISNUMBER(I11),ISNUMBER(G11),ISNUMBER(E11)) ,SUM((E11+G11+I11)-(D11+F11+H11)),if(AND(ISNUMBER(E12),ISNUMBER(G12)),SU M((E12+G12)-(D12+F12)),if(AND(ISNUMBER(E12),SUM(E12-D12)),"")))

Probably now got the ) wrong as just did this on my IPAD whilst watching the late news, out walking tomorrow but will check in the evening.

Brian

 

[BobNTN]

Worked after I took the third 'and' out since it was only testing one cell in the third part.

In case it would be helpful to anyone else, it works as:

=IF(AND(ISNUMBER(I10),ISNUMBER(G10),ISNUMBER(E10)),SUM((E10+G10+I10)-(D10+F10+H10)),IF(AND(ISNUMBER(E10),ISNUMBER(G10)),SUM((E10+G10)-(D10+F10)),IF(ISNUMBER(E10),SUM(E10-D10),"")))

I really appreciate that Brian.