# 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

###### Who is Participating?
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.

EngineerCommented:
Author Commented:
Hello,

Please find the file attached below.
Error.xlsm
EngineerCommented:
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.
Author Commented:
Hello,

I explained in detail in this second file attached below.
Thank you
Error2.xlsm
EngineerCommented:
This is what I see when I open the file

Author 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
EngineerCommented:
Unless I am able to see the error happening I cannot address it.
Author Commented:
Should I replace "," by "." in the code?
EngineerCommented:
Not sure. Give it a try.
EngineerCommented:
You might even change it to something different like # or ^
Author Commented:
How to do?
EngineerCommented:
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
``````
Author Commented:
It gaves the same result. :(
EngineerCommented:
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?
EngineerCommented:
Can you send your windows settings? Here is mine
Author Commented:
Author Commented:
Author Commented:
French language. It will be better in Excel if it is possible
EngineerCommented:
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
``````

Experts Exchange Solution brought to you by

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

Author 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
Author Commented:
Thank you Saqib. You are genius
###### 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.