If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
problem with formula
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(C20,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(C20,F1,"") and so on down th column column E =SUM(IF(C50,G5-H5)) column F =SUM(IF(C50,(J4+C5-G5),IF(G50,(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 |
#2
|
|||
|
|||
problem with formula
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 "Bowmanator" wrote: 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(C20,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(C20,F1,"") and so on down th column column E =SUM(IF(C50,G5-H5)) column F =SUM(IF(C50,(J4+C5-G5),IF(G50,(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 |
#3
|
|||
|
|||
problem with formula
hope this helps
http://www.savefile.com/files/2073433 "Francis" wrote: 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 "Bowmanator" wrote: 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(C20,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(C20,F1,"") and so on down th column column E =SUM(IF(C50,G5-H5)) column F =SUM(IF(C50,(J4+C5-G5),IF(G50,(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 |
#4
|
|||
|
|||
problem with formula
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(C20,F1,"") to this =IF(A2="","",IF(C20,F1,"")) -- Please remember to indicate when the post is answered so others can benefit from it later. "Bowmanator" wrote: 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(C20,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(C20,F1,"") and so on down th column column E =SUM(IF(C50,G5-H5)) column F =SUM(IF(C50,(J4+C5-G5),IF(G50,(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 |
#5
|
|||
|
|||
problem with formula
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(G50,J4,"") column I- =SUM(IF(C50,G5-H5)) column J- =SUM(IF(C50,(J4+C5-G5),IF(G50,(J4-G5)))) hope this clears it up a little bit thanks again Dave "KC Rippstein" wrote: 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(C20,F1,"") to this =IF(A2="","",IF(C20,F1,"")) -- Please remember to indicate when the post is answered so others can benefit from it later. "Bowmanator" wrote: 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(C20,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(C20,F1,"") and so on down th column column E =SUM(IF(C50,G5-H5)) column F =SUM(IF(C50,(J4+C5-G5),IF(G50,(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 |
#6
|
|||
|
|||
problem with formula
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(c50,g5-h5,0) J5: =j4-h5-i5+c6 Regards, Fred. "Bowmanator" wrote in message news hope this helps http://www.savefile.com/files/2073433 "Francis" wrote: 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 "Bowmanator" wrote: 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(C20,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(C20,F1,"") and so on down th column column E =SUM(IF(C50,G5-H5)) column F =SUM(IF(C50,(J4+C5-G5),IF(G50,(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 |
#7
|
|||
|
|||
problem with formula
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 "Bowmanator" wrote: 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(G50,J4,"") column I- =SUM(IF(C50,G5-H5)) column J- =SUM(IF(C50,(J4+C5-G5),IF(G50,(J4-G5)))) hope this clears it up a little bit thanks again Dave "KC Rippstein" wrote: 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(C20,F1,"") to this =IF(A2="","",IF(C20,F1,"")) -- Please remember to indicate when the post is answered so others can benefit from it later. "Bowmanator" wrote: 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(C20,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(C20,F1,"") and so on down th column column E =SUM(IF(C50,G5-H5)) column F =SUM(IF(C50,(J4+C5-G5),IF(G50,(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 |
Thread Tools | |
Display Modes | |
|
|