• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 58
  • Last Modified:

Stock cost error

Good morning everyone, hello Saqib Husain, Syed

I am refering to the discusion https://www.experts-exchange.com/questions/29084963/Excel-formula-for-calculating-the-Cost-Value-on-FIFO-basis.html 

The formula gives a false calculation when i changed the price.

The bying price of AAA ligne 3  E:3 is 501,00. If i put  501,93, the formula calculate the same cost which is 12.525,00 as for 501,00 in I:9 but the correct result is  25*501,93 = 12.548,25 not 12.525,00. Thank you

Please see the file attached.

Thank you for reading
0
Adil Kh
Asked:
Adil Kh
  • 11
  • 10
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Please upload a working excel file showing the problem.
0
 
Adil KhAuthor Commented:
Hello,

Please find the file attached below.
Error.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
Were you able to enter 501.93?

The file you gave does not have 501.93. It has 501.00.

When I enter 501.93 I get 12,584.25.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Adil KhAuthor Commented:
Hello,

I explained in detail in this second file attached below.
Thank you
Error2.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
This is what I see when I open the file

When I open the file
0
 
Adil KhAuthor Commented:
OOOH now i understand.

I have numbers with "," you have numbers with "." When I put "." it gives a correct answer when i put "," the formula gives a wrong answer.

Can you help me with this problem?

Thank you
0
 
Saqib Husain, SyedEngineerCommented:
Unless I am able to see the error happening I cannot address it.
0
 
Adil KhAuthor Commented:
Should I replace "," by "." in the code?
0
 
Saqib Husain, SyedEngineerCommented:
Not sure. Give it a try.
0
 
Saqib Husain, SyedEngineerCommented:
You might even change it to something different like # or ^
0
 
Adil KhAuthor Commented:
How to do?
0
 
Saqib Husain, SyedEngineerCommented:
Try this code
Option Explicit
Function fifoval(q As Range, Optional details As String) As Variant
Application.Volatile (True)
    Dim i As Integer
    Dim qstr As String
    Dim pstr As String
    Dim cqty As Integer
    Dim prc As Double
    Dim qty As Integer
    Dim ctr As Integer
    Dim dstr As String
    Dim amt As Double
    'Stop
    For i = 2 To q.Row - 1
        If Cells(i, 1) = Cells(q.Row, 1) Then 'Counting from row 2 to row q-1
            Select Case Cells(i, 2) 'Selecting the Item range
                Case "Buy" ' In case of byuing
                    qstr = qstr & Cells(i, 4) & "#" '???????????
                    pstr = pstr & Cells(i, 5) & "#" '???????????
                Case "Sell" ' In case of selling
                    qty = Cells(i, 4) 'selling quantity
                    Do While qty > 0 'As the selling quantity is positive
                        
                        cqty = Val(qstr) '???????????
                        If cqty = 0 Then
                            fifoval = "Not enough balance"
                            Exit Function
                        End If
                        Select Case True
                            Case cqty = qty
                                qstr = Replace(qstr, cqty & "#", "", , 1)
                                pstr = Replace(pstr, Val(pstr) & "#", "", , 1)
                                qty = qty - cqty
                            Case cqty > qty
                                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                                qty = 0
                            Case cqty < qty
                                qstr = Replace(qstr, cqty & "#", "", , 1)
                                pstr = Replace(pstr, Val(pstr) & "#", "", , 1)
                                qty = qty - cqty
                            Case cqty = 0
                                fifoval = "Not enough balance"
                                Exit Function
                        End Select
                        ctr = ctr + 1
                        If ctr > 1000 Then End: Stop
                    Loop
            End Select
        End If
    Next i
    qty = Cells(q.Row, 4)
    Do While qty > 0
        
        cqty = Val(qstr)
        If cqty = 0 Then
            fifoval = "Not enough balance"
            Exit Function
        End If
        prc = Val(pstr)
        Select Case True
            Case cqty = qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty & "#", "", , 1)
                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                qty = qty - cqty
            Case cqty > qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                qty = 0
            Case cqty < qty
                dstr = dstr & IIf(dstr = "", "", " + ") & cqty & " * " & prc
                amt = amt + cqty * prc
                qstr = Replace(qstr, cqty & "#", "", , 1)
                pstr = Replace(pstr, Val(pstr) & "#", "", , 1)
                qty = qty - cqty
        End Select
        ctr = ctr + 1
        If ctr > 1000 Then End: Stop
    Loop
    If details = "" Then
        fifoval = amt
    Else
        fifoval = dstr
    End If
End Function

Open in new window

0
 
Adil KhAuthor Commented:
It gaves the same result. :(
0
 
Saqib Husain, SyedEngineerCommented:
What language have you installed excel for? Do I have to change only in excel or would I have to change it in Windows as well?
0
 
Saqib Husain, SyedEngineerCommented:
Can you send your windows settings? Here is mine
Regional settings
0
 
Adil KhAuthor Commented:
Sans-titre.png
0
 
Adil KhAuthor Commented:
Sans-titre.png
0
 
Adil KhAuthor Commented:
French language. It will be better in Excel if it is possible
0
 
Saqib Husain, SyedEngineerCommented:
Now try this. Check data thoroughly.
Option Explicit
Function fifoval(q As Range, Optional details As String) As Variant
Application.Volatile (True)
    Dim i As Integer
    Dim qstr As String
    Dim pstr As String
    Dim cqty As Integer
    Dim prc As Double
    Dim qty As Integer
    Dim ctr As Integer
    Dim dstr As String
    Dim amt As Double
    'Stop
    For i = 2 To q.Row - 1
        If Cells(i, 1) = Cells(q.Row, 1) Then 'Counting from row 2 to row q-1
            Select Case Cells(i, 2) 'Selecting the Item range
                Case "Buy" ' In case of byuing
                    qstr = qstr & CDbl(Cells(i, 4)) & "#" '???????????
                    pstr = pstr & CDbl(Cells(i, 5)) & "#" '???????????
                Case "Sell" ' In case of selling
                    qty = Cells(i, 4) 'selling quantity
                    Do While qty > 0 'As the selling quantity is positive
                        
                        cqty = Val(Replace(qstr, ",", ".")) '???????????
                        If cqty = 0 Then
                            fifoval = "Not enough balance"
                            Exit Function
                        End If
                        Select Case True
                            Case cqty = qty
                                qstr = Replace(qstr, cqty & "#", "", , 1)
                                pstr = Replace(pstr, Val(Replace(pstr, ",", ".")) & "#", "", , 1)
                                qty = qty - cqty
                            Case cqty > qty
                                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                                qty = 0
                            Case cqty < qty
                                qstr = Replace(qstr, cqty & "#", "", , 1)
                                pstr = Replace(pstr, Val(Replace(pstr, ",", ".")) & "#", "", , 1)
                                qty = qty - cqty
                            Case cqty = 0
                                fifoval = "Not enough balance"
                                Exit Function
                        End Select
                        ctr = ctr + 1
                        If ctr > 1000 Then End: Stop
                    Loop
            End Select
        End If
    Next i
    qty = Cells(q.Row, 4)
    Do While qty > 0
        
        cqty = Val(Replace(qstr, ",", "."))
        If cqty = 0 Then
            fifoval = "Not enough balance"
            Exit Function
        End If
        prc = Val(Replace(pstr, ",", "."))
        Select Case True
            Case cqty = qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty & "#", "", , 1)
                pstr = Replace(pstr, Val(Replace(pstr, ",", ".")) & "#", "", , 1)
                qty = qty - cqty
            Case cqty > qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                qty = 0
            Case cqty < qty
                dstr = dstr & IIf(dstr = "", "", " + ") & cqty & " * " & prc
                amt = amt + cqty * prc
                qstr = Replace(qstr, cqty & "#", "", , 1)
                pstr = Replace(pstr, Val(Replace(pstr, ",", ".")) & "#", "", , 1)
                qty = qty - cqty
        End Select
        ctr = ctr + 1
        If ctr > 1000 Then End: Stop
    Loop
    If details = "" Then
        fifoval = amt
    Else
        fifoval = dstr
    End If
End Function

Open in new window

1
 
Adil KhAuthor Commented:
Good morning,

I am sorry for being late. I had a prblem connexion with my labtop navigator during the weekEnd.

It works perfectly. I am happy

Thank you Saqib Husain, Syed
0
 
Adil KhAuthor Commented:
Thank you Saqib. You are genius
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now