problem with formulas (2)


B

Bowmanator

Sorry for the confusion and repost, i'll try to explain better this time.
here's a copy of the actual sheet http://www.savefile.com/files/2073433
and below is an example of it

this is an on going oil log, that has to account for all oil used and were
it was used.

row 4, in this example i'm starting with a new batch amount (C4), and i
produced (G4), since this is a new batch there is no previous batch, (H4)
should be blank and I4 (amount used from previous batch) should equal G4, and
J4 is the remainning balance C4-G4.

row 5, i have no new batch (C5) so this cell is empty, but i produced (G5)
and since G5 is less than J4 cells H5 and I5 should be blank (i can't get H5
to go blank) and J5 is the result of J4-G5

row 6, now i have an amount in C6 (batch amount) and produced in G6, but
since G6 is more than J5 i need H6 to equal J5 and I6 to equal the difference
between G6 and H6, (H6+I6=G6) with the formula i'm using it seems to be doing
this. and J6 should equal J5+C6-G6

this sheet will be onging.

C G H I J
4 127,423 54,000 54,000 73,000
5 43,200 30,223
6 149,481 36,000 30,223 5,777 143,704

the formulas i'm using are.

column H- =IF(G5>0,J4,"")
column I- =SUM(IF(C5>0,G5-H5))
column J- =SUM(IF(C5>0,(J4+C5-G5),IF(G5>0,(J4-G5))))

hope this clears it up a little bit

thanks again
Dave
 
Ad

Advertisements

B

Bowmanator

it's hard for me to explain and i know it's even tougher to understand.

it seems that as long as the amount in cell G5 is greater than the amount in
cell J4 the formulas will work, but if cell C5 is blank and G5 is less than
J4 cell H5 is showing amount in J4 (it needs to be blank) and cell I5 shows a
negetive number. (it also needs to be blank)

the formulas just aren't doing what i want, the example i showed is what i
want it to look like, but on the attachment cell H5 is showing the results
from J4, but since cell G5 is less than J4 cell H5 and I5 need to be blank
and it isn't.
 
K

Kassie

In cell G5 insert =IF(OR(C5="",G5<J4),"",IF(G5>0,J4,""))
Copy down
In cell I5 insert =IF(OR(C5="",G5<J4),"",IF(C5>0,G5-H5))
Also copy down. Is this what you are looking for?

--
HTH

Kassie

Replace xxx with hotmail
 
F

Francis

try this in H5

=IF(AND(C5="",G5<J4),"",J4)

Does this do what you want?
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
F

Francis

Oops, I've missed this formula in my earlier solution

In I5, try this formula
=IF(ISERROR(G4-H4),"",IF(G4-H4<0,"",G4-H4))

Does this handle your situation in column I as well?
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
Ad

Advertisements

B

Bowmanator

Kassie, Thank you

it seems to work great and the way i visioned it, i just had to insert the
first formula in H5 instead of G5.

again many thanks
 
Ad

Advertisements

K

Kassie

Yes you're right. In G5 that would create a cirdular reference hey. Glad I
could help!
--
HTH

Kassie

Replace xxx with hotmail
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top