Formula (Excel 2003)

Topic

New Reply

My friend has given me a problem to resolve, assuming me an excel guy, in attached sheet. For me it is a tough one to get in to. The problem is, In Cell “K7”, first it will look in op bal of particular type say “IND.” in Col. E, F, G and so on, if there is no sufficient qty in op bal, it will look in PUR (ColK5) & then minus the issue qty (Cell K6) from it. In “Remarks” Col (K8) identification of lot changed is required. The gue is not looking for macro, simple formulas are required.

Any idea?

Reply | Quote

Replies

Er, your sample workbook already contains formulas. What exactly is the question?

Reply | Quote

Er, your sample workbook already contains formulas. What exactly is the question?

Yeah Hans, but that is an unconsistant formula. See the Col. Q, wherein the qty is -ve, having op. bal of 775. Thats the problem.

Reply | Quote

So what exactly do you expect the result to be? Please explain fully and clearly.

Reply | Quote

So what exactly do you expect the result to be? Please explain fully and clearly.

Let me make it clear. In any case, ledger qty should not be -ve. How can I make excel understand to look first date op bal, then 2nd date op bal and so on. and at last, if there is no op bal qty. then look in the date wise purchase, to issue the qty. Second, “Lot Changed” is also required in Remarks field. I have done something in attached sheet, but I am not comfortable. There must be some scientific method of calculation. Results are required in my first attachement only.

Thanks for having patience.

Reply | Quote

I’m very sorry, I don’t understand. I hope that someone else will be able to help you.

Reply | Quote

I have to agree with Hans on not understanding what you want or need. I am also confused on how the 2nd file relates to the first file attached.

Perhaps if for the cells that have incorrect values, you could tell us what the correct values are and how those values are obtained, someone could help you more…

Some observation I will make on Book1:
If your values should not negative because of values in E,F,G perhaps you should include the sum of EFG in K7 and not just e:
=SUM(E5:G5)-K6

Though if the dates of EFG are important, then I fail to see why any of the cols are included that early (E6 is May 13) so should not be included until the 13th of the month in Cols AI/AJ and F not until cols AO/AP and Col G until BG/BH. It seems you are missing the “Op Bal” for May 1 which is what (I speculate) should be used in K7

If this is meant to be some “running total” of the items added and the items removed day by day instead of looking at the total on any date, why not just sum from some starting point the number added and the number subtracted at any given date…

Though as I said I admit that I don’t completely understand what you intent is…

Steve

Reply | Quote

I have to agree with Hans on not understanding what you want or need. I am also confused on how the 2nd file relates to the first file attached.

Perhaps if for the cells that have incorrect values, you could tell us what the correct values are and how those values are obtained, someone could help you more…

Some observation I will make on Book1:
If your values should not negative because of values in E,F,G perhaps you should include the sum of EFG in K7 and not just e:
=SUM(E5:G5)-K6

Though if the dates of EFG are important, then I fail to see why any of the cols are included that early (E6 is May 13) so should not be included until the 13th of the month in Cols AI/AJ and F not until cols AO/AP and Col G until BG/BH. It seems you are missing the “Op Bal” for May 1 which is what (I speculate) should be used in K7

If this is meant to be some “running total” of the items added and the items removed day by day instead of looking at the total on any date, why not just sum from some starting point the number added and the number subtracted at any given date…

Though as I said I admit that I don’t completely understand what you intent is…

Steve

Hi Hans/Steve,

First of all, I appologies to waste your precisious time. Here is the problem in more illustrated way.

I have op bal (cl bal of last month) in 3 lots and have issued 10 kg on day 1. Now, to calculate the Closing bal on day 1, it will first look in lot 1 of op bal. There is sufficent qty to issue and bal would be 0. Since lot 1 is already consumed, I have to take 5 kg from lot 2 and bal 10 kg from lot 3 to issue 15 kg on 2nd day. In op bal, lot 1 & 2 are fully consumed and in lot 3, 5 kg is bal. To issue 25 kg on day 3, it will take 5 kg from lot 3 and for bal 20 kg, will look in to day wise purchase upto 3rd (not beyond issuing date) and calculate the bal ( 5 kg in present case). The whole calculation can be arrived through a simple calculation but the only problem is to identify the lot wise cl bal, which required in remarks field.

Hope this will make some sense.

Steve, second attachment is nothing else but revised version of first attachment, in which I have tried to solve the proble. The only difference is change of format in second attachment, which is not acceptable.

Reply | Quote

My understanding is that you want:

Formulas in Cells B6 to I6 to give some count value. Could you attach what values you think should be in those cells (and a little of the logic)? I have an idea of what you want but I want to see the values to make sure the formula works

I am thinking of something in E6 like (copied across the cols in row 6)
=SUM($B$4:E4)-SUM($B$5:E5)

But I don’t know what you you want in B6:D6 Copying the above formula will yield the running total (10,15, 30, respectively). It also will not prevent the negative value in I6 due to trying to issue 15 items you do not have…

Formulas in Cells B7 to I7 to give some text response. Could you attach what values you think should be in those cells (and a little of the logic). I really have no idea of what kind of remark you want in each of those cells…

It appears that some intermediate columns may be neccessary for these remarks (a running total purch from row 4 and a running total issued from row 5) to get the lot transitions, but what it is depends on what you want displayed exactly…

Steve

Reply | Quote

My understanding is that you want:

Formulas in Cells B6 to I6 to give some count value. Could you attach what values you think should be in those cells (and a little of the logic)? I have an idea of what you want but I want to see the values to make sure the formula works

I am thinking of something in E6 like (copied across the cols in row 6)
=SUM($B$4:E4)-SUM($B$5:E5)

But I don’t know what you you want in B6:D6 Copying the above formula will yield the running total (10,15, 30, respectively). It also will not prevent the negative value in I6 due to trying to issue 15 items you do not have…

Formulas in Cells B7 to I7 to give some text response. Could you attach what values you think should be in those cells (and a little of the logic). I really have no idea of what kind of remark you want in each of those cells…

It appears that some intermediate columns may be neccessary for these remarks (a running total purch from row 4 and a running total issued from row 5) to get the lot transitions, but what it is depends on what you want displayed exactly…

Steve

Thanks Steve, I was dying to see someone here.

I have done something in attached sheet. It served the purpose except the remarks, wherein I need to put the date from which lot is relates. In 01 june, I have consumed full qty of lot pertainig to 15 May & lot has been changed for 02 June issue. In that case, I need a lot identification something like”LOT CHANGED 20-May”. This is also required to carry forward the balance for next month.

Thanks again

Reply | Quote

You are confusing the issue again by putting in formulas.

If the formulas do not give you want you want, why add them. If they do, I don’t understand the question.

You are also changing your example without answering the question. You have 3 example files that look similar. For each one and for each cell that you want formulas in, instead of putting in formulas show us the spreadsheet without those formulas and then in a separate sheet in the workbook, show us the RESULTS of what you want to achieve with those formulas.

With the No formulas and the values we may be able to create those formulas. [Note if you want the result of a formula to be null (appear “blank”), just entered in the cell and we can add this to the formula] If you can explain the logic for a result it may also help …

You may want to color the cells that you want formulas created for so it is clear which cells we should create formulas and which cells any existing formulas should stay…

Steve

Reply | Quote

I have read this thread and don’t undersatnd what you want to achieve.
Does the formulas in your attachment show the result you want?
If yes, what are you after?
If no, I suggest that you post a new sample with your original data in a table
and another table with the result you are after.

Reply | Quote

I have read this thread and don’t undersatnd what you want to achieve.
Does the formulas in your attachment show the result you want?
If yes, what are you after?
If no, I suggest that you post a new sample with your original data in a table
and another table with the result you are after.

I am in a “never expected” position. However, fresh sheet is attached with following requirements :

1. In balance fields (coloured in green), need closing balance for the day.
2. In remarks fields (coloured in red), need identification, if previous lot is consumed and fresh lot is required to issue the material.

Reply | Quote

You still have not provided what we have asked for (on several occasions)”
For this workbook in each of the cells marked, what VALUES do you want the formulas to result in? COuld you provide an “after copy” of the results you expect in those cells.

Steve

Reply | Quote

You still have not provided what we have asked for (on several occasions)”
For this workbook in each of the cells marked, what VALUES do you want the formulas to result in? COuld you provide an “after copy” of the results you expect in those cells.

Steve

Here is the copy with required results.

Reply | Quote

Here is the copy with required results.

I have no option but to leave this topic un-answered. I know this is not that tough but probably messed up with different attachments. Any way, better luck next time.

Reply | Quote

I have no option but to leave this topic un-answered. I know this is not that tough but probably messed up with different attachments. Any way, better luck next time.

Again, how to cut this short. Will serve the purpose.

=CHOOSE(D$3,PUR!$D5,SUM(PUR!$D5+PUR!$F5),SUM(PUR!$D5+PUR!$F5+PUR!$H5),SUM(PUR!$D5+PUR!$F5+PUR!$H5+PUR!$J5),SUM(PUR!$D5+PUR!$F5+PUR!$H5+PUR!$J5+PUR!$L5),SUM(PUR!$D5+PUR!$F5+PUR!$H5+PUR!$J5+PUR!$L5+PUR!$N5))…………………..

Reply | Quote

Try

=SUMPRODUCT((MOD(COLUMN(PUR!$D:$IV),2)=0)*(COLUMN(PUR!$D:$IV)<=(2*D$3+2))*PUR!$D5:$IV5)

Reply | Quote

Try

=SUMPRODUCT((MOD(COLUMN(PUR!$D:$IV),2)=0)*(COLUMN(PUR!$D:$IV)<=(2*D$3+2))*PUR!$D5:$IV5)

Thanks Hans but still struggling with this one

=CHOOSE(D$3,PUR!$E5,SUM(PUR!E5+PUR!G5),SUM(PUR!E5+PUR!G5+PUR!I5),SUM(PUR!E5+PUR!G5+PUR!I5+PUR!K5),SUM(PUR!E5+PUR!G5+PUR!I5+PUR!K5+PUR!M5),SUM(PUR!E5+PUR!G5+PUR!I5+PUR!K5+PUR!M5+PUR!O5))

Either I have lost my balance OR I dont deserve to be here any more as I have made every possible unfruitful change in the formula provided by you.

Pls help.

Reply | Quote

I did it with some intermediate rows…

I don’t think trying to term them into complicated arrays will work, though I didn’t attempt it…

NOTE: I got a different lot change than your example, so either my logic or yours is wrong…

Steve

Reply | Quote

I did it with some intermediate rows…

I don’t think trying to term them into complicated arrays will work, though I didn’t attempt it…

NOTE: I got a different lot change than your example, so either my logic or yours is wrong…

Steve

Thanks Steve for your valuable solutions. I am working around and will let you know the results.

Reply | Quote

I did it with some intermediate rows…

I don’t think trying to term them into complicated arrays will work, though I didn’t attempt it…

NOTE: I got a different lot change than your example, so either my logic or yours is wrong…

Steve

Thanks Steve for your continious efforts to help me out. With the great help provided, I have settled the issue (with some requirements left aside). Thanks a lot.

Reply | Quote