Forum Discussion
Ryan_Foster
Feb 09, 2023Copper Contributor
Need help with product formula showing up wrong answers
Hello, I am having issues with having my spreadsheet auto multiply numbers to build a running total. i have attached a screenshot. If anyone could help sooner rather than later that would be great as...
PeterBartholomew1
Feb 10, 2023Silver Contributor
It would be interesting to know how you finished up with text for what should clearly be numeric data; Excel tries very hard to convert input to numbers, producing fractions or dates where nothing of the sort was intended. If you can't fix the problem at source, then the VALUE function would provide an alternative to the manual repairs described by Riny_van_Eekelen.
= PRODUCT(VALUE(B2:C2))
For 365 users the entire column may be processed at a single step by
= BYROW(data, Productλ)
where
Productλ
= LAMBDA(v, PRODUCT(VALUE(v)))
As Riny also suggests, it might be clearer to specify the quantities and costs as separate quantities, converting each to a number first.