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.
...
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?
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))- Thank you for the reply, however I was able to make it work by using your original formula in column D
now the column D formula is =round(C/B,2)
that fixed all the cells that were dependent on the answer in column D.
