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
Adil KhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.