Forum Discussion
excel not calculating money correctly
In my workbook I have the following columns
A , B , C , D , E , F ,
Item, Qty/ case, Cost/ case, Cost/ unit, # items sold, COGS.
Twix, 36 , $20.68 , $0.57 , 10 , $5.74
D=C/B
F=D*E
I want to F to be showing $5.70 since I am calculating money.
The formula is using more than 2 decimal places in column D to calculate even though I only have it showing 2 decimal places.
I have columns D,C,&F formatted to currency.
5 Replies
Justin,
Use this formula in column F:
=ROUND(D2*E2,1)
- That kind of worked. so now my 10 twix now show as 5.70.
however now I have items that are, for example, showing .62 cents ea. and 2 sold and column F is showing 1.20 instead of 1.24. I changed the formula from
=round(D2*E2,1) to =round(D2*E2,2) and then it showed 1.24. However, I would like it if i didn't have to adjust each formula in column F to show the correct amount as there are 115 items or rows that i am calculating info for. Is there another method?Hi Justin,
It's unusual to have a data in a column have mixed formats!
The data in a column is supposed to carry one format based on the data type.
I recommend you to rearrange the dataset somehow to separate each data type in a separate column.
However, in this unusual case, I hope this formula works well:
=IF(OR(CELL("format",D2)="G",CELL("format",D2)="F2",CELL("format",D2)="P2"),D2*E2,ROUND(D2*E2,1))
- Yes, I was having the same issue. Resolved!
Thank You!
