Need help with product formula showing up wrong answers

Copper 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 i am facing a deadline.

3 Replies

Re: Need help with product formula showing up wrong answers

Multiply and divide numbers in Excel

=PRODUCT(A2,B2)

Example file is included

NikolinoDE

I know I don't know anything (Socrates)

Re: Need help with product formula showing up wrong answers

@Ryan_Foster I suspect that both columns B and C contain texts that look like numbers. Multiplying two texts with the PRODUCT function results in zero. You need to fix that first. Format both columns as Number and select the entire column B. On the Data ribbon, select Text-to-columns and directly press Finish. Don't change anything else. Repeat for column C. Now, the texts have become numbers and the formula shall work.

By the way, no need to use PRODUCT. You can just use =B2*C2 and it would have triggered a VALUE error to indicate something is wrong with the underlying "numbers". As said, PRODUCT just returns zero as it ignores texts.

Re: Need help with product formula showing up wrong answers

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.