problem with formula

B

Bowmanator

new at this and everything i know i learned from here and i Hope you guys can
help me with this one.
I'm working on this spread sheet below, i know what i want it to do just
hard to explain. it's performing somewhat like i want but i needs to be
tweeked.

column E and F seems to be doing what it suppose to. E seems to be the
problem.

in column D i'm using =IF(C2>0,F1,"") and that seems to work if there's an
entry in column A, but if A2 is blank i need D2 to be blank and F2 is the
result of C2-F1. now if you look at row 3 it's doing exacly like it should,
D3 is showing that it took F3 + E7 to equal C3, and F3 is the result of A3-E3.


the formulas i'm using are
column D =IF(C2>0,F1,"") and so on down th column
column E =SUM(IF(C5>0,G5-H5))
column F =SUM(IF(C5>0,(J4+C5-G5),IF(G5>0,(J4-G5))))

sorry for be so long winded, didn't know any other way to explain it.
now my brain hurts.

thanks
Dave

A B C D
E F
new amount production amount used amount
remanning
batch produced in lbs. from previous used
from batch
batch
new batch
1 127,423 1,500 54,000
54,000 73,423
2 1,200 43,000 73,423
30.223
3 149,481 1,000 36,000 30,223
5,777 143,704
4 1,350 48,600 143,704
95,104
5 2,000 72,000 95,104
23,104
6 136,364 1,525 54,900 23,104
31,796 104,568
 
F

Francis

you may have explain your situation, but I still don't follow what you want to
do. Maybe it would be easiler to post your sample and expected result
Alternatively, post ypur sample at a host free site like savefile
and tell us the link to it
--
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
 
K

KC Rippstein hotmail com>

Sorry, you're explanation is hard to follow and missing stuff. What is in
columns G and J? Why is row 3 referencing data in row 7? How is it in row 2
you're referencing the title of column F (F1)?

If you could explain what is in each column of data and what you want each
calculated column to do, it would help a lot.

And each time you've used SUM below, you don't need it. Get rid of SUM.

For one of your questions, you might change this
=IF(C2>0,F1,"")
to this
=IF(A2="","",IF(C2>0,F1,""))
 
B

Bowmanator

Sorry for the confusion, 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
 
F

Fred Smith

You're complicating your life by unnecessarily using the Sum function, and
your If statements are too complicated. You didn't learn that stuff from
us. Also, you make it difficult for Excel to find your data when you leave
blank lines and columns. Delete rows 1 and 2, and column A. If you want the
white space, do it with margins in Print Setup or change the height of cells
or rows.

My bet is you want the following:
G4: =f4*36
H4: 0
I4: =g4
J4: =c4-g4

G5: f5*36
H5: =min(j4,g5)
I5: =if(c5>0,g5-h5,0)
J5: =j4-h5-i5+c6

Regards,
Fred.
 
B

Bowmanator

soory for the double post, i kept grtting an error when i clicked Post. had
to write this 3 times.

didn't know if it went through ok

thanks
 

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