Aug 02 2018 09:14 AM
I'm looking for a way for a formula to multiply the value of a cell based on the count of the numbers within it.
For example, the formula now takes the dollar value in one cell X quantity of another cell (i.e, $1200.00 x 2) to produce the result in a third cell. But within another cell is the applicable number of quarters in a year that money has to be spent. They are displayed as numbers separated by commas; for instance, 1,3,4. Those are the quarters of the year in which a specific amount of money will be spent. I need that cell to count as a number 3 for multiplication purposes so the total output to another cell represents $1200 X quantity X number of quarters in the year (in this example the output should total $7200 ($1200 X 2 X 3)
Attached is a screenshot example. The current formula accounts for the value of D14 X E14 (output in F14) but does not factor in G14, which should represent a multiplier of 2 (that output would go in K14.
Does anyone know what the formula would be in order to produce that result? I am using Excel 2016 if that helps. Thank you very much and greatly appreciate any assistance. Good day all.
Aug 02 2018 03:46 PM
SolutionHi,
If I understood correctly the task is to calculate the number of digits in the text where could be from 1 to 4 digits separated by commas, like this
That could be classical formula (column B)
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
or for your case bit shorter one (column C)
=INT(LEN(A1)/2)+1
and attached
Aug 02 2018 04:07 PM
Yes, that was exactly what I was looking for. Thank you!
Aug 02 2018 03:46 PM
SolutionHi,
If I understood correctly the task is to calculate the number of digits in the text where could be from 1 to 4 digits separated by commas, like this
That could be classical formula (column B)
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
or for your case bit shorter one (column C)
=INT(LEN(A1)/2)+1
and attached