Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Er, your sample workbook already contains formulas. What exactly is the question?

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780643' date='19-Jun-2009 13:14']Er, your sample workbook already contains formulas. What exactly is the question?[/quote]

    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.
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So what exactly do you expect the result to be? Please explain fully and clearly.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780648' date='19-Jun-2009 13:32']So what exactly do you expect the result to be? Please explain fully and clearly.[/quote]

    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.
    Attached Files Attached Files
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm very sorry, I don't understand. I hope that someone else will be able to help you.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='780665' date='19-Jun-2009 17:47']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[/quote]

    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.
    Attached Files Attached Files
    Regards
    Prasad

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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 B66 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

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='780761' date='20-Jun-2009 10:55']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 B66 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[/quote]

    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
    Attached Files Attached Files
    Regards
    Prasad

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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 <null> 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

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='780787' date='20-Jun-2009 14:49']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.[/quote]

    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.
    Attached Files Attached Files
    Regards
    Prasad

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  15. #15
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='780976' date='22-Jun-2009 11:27']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[/quote]

    Here is the copy with required results.
    Attached Files Attached Files
    Regards
    Prasad

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •