If 2 cells not blank, then if 1 is not blank, then if the other is not blank

[Einkorn]

Hi there,

I have a spreadsheet to track inventory locations.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bags[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20[/TD]
[TD]Quincy[/TD]
[TD]OL[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]OL[/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Quincy[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If there is no entry in Bags (C6), leave Balance (F6) blank. Row 2 is just to show the beginning inventory for clarification.

If there is an entry in both the From (D3) and To (E3) columns, I want the Balance (F2) to stay the same --> (F3=F2)

If there is an entry in From (D4) only, I want it to subtract Bags (C4) from Balance (F3) --> (F3-C4)

If there is an entry in To (E5) only, I want it to add Bags (C5) to Balance (F4) --> (F4+C5) [* PROBLEM CHILD *]


Here's what I used before I realized I needed the last step of adding when the "To" column was filled in:

=IF(ISBLANK(C4),"",(IF(OR(D4="",E4=""),F3-C4,F3)))

I'm at a loss how to add this last step. What should I add, or is there a better formula to use here?

 

[ace19852]

Hi and welcome to the board I don't know if there is a shorter way other than using a formula with multiple if's

I have taken your if statements 2,3 and 4 in order then the final test "If there is no entry in Bags (C6)" is blank wich is at the end of my statement.

Try in F2 and copy down

=IF(AND(D2<>"",E2<>""),F1,IF(AND(D2<>"",E2=""),F1-C2,IF(AND(D2="",E2<>""),F1+C2,"")))

Cheers

 

[Einkorn]

Yes! That does exactly what I want. Thank you so much!

 

[mikerickson]

Try putting this in F3 and dragging down
=F2+(E3<>"")*C3-(D3<>"")*C3

Perhaps wrapping it in =IF(C3="", "", formula)

 

[ace19852]

Hey Mike nice formula I just tried it out and had to run it through the evaluate formula process. It just struck me what you were doing and what a clever little excel trick to test for true (1) or false (0) so that ...... if it's a 1 i.e. cell not empty, 1*20 say = 20 and if it's 0 i.e. cell empty it's 0*20 = 0 , maths 101

What I mean is I learned another little gem on how to shorten formula.

Guess I gotta get up early to catch you out.

Thanks for a brilliant insight

Cheers

 

[Einkorn]

mikerickson,

It worked by itself, but when I tried to wrap it in the isblank formula, it didn't. Here's what I did:

=IF(ISBLANK(C4),"",F3+(E4<>"")*C4-(D4<>"")*C4

This returned the #VALUE! error.

Then I tried an adjustment:

=IF(C4="","",F3+(E4<>"")*C4-(D4<>"")*C4)

which worked as it should.

Would you please explain your formula to me and why it works? Particularly the * part.

Thanks!

 

[Einkorn]

Here's another little irritating thing. When I copied the formula down, the cells did not keep my conditional formatting which alternated a shading on each row using MOD. How can I make it keep that formatting at the same time as filling down? It's kept the formatting before when I've filled other things down. =p

Thanks!

 

[mikerickson]

When you copy a cell down, it includes the formatting (including CF).
To move a formula while preserving the formatting of the receiving cells, you need to use Copy / PasteSpecial >> formulas.