Feb 09 2023 10:33 AM
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.
Feb 09 2023 10:43 PM
Multiply and divide numbers in Excel
=PRODUCT(A2,B2)
Example file is included
If this was not requested of you, please provide more details about your project.
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
Feb 09 2023 10:51 PM
@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.
Feb 10 2023 01:52 AM
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.