String-Based Multiplication in Spreadsheets

Other Questions Google Sheets
1

I’m working on a spreadsheet and need help with a tricky calculation. Here’s what I’m trying to do:

  1. I have a list of string values.
  2. For the first value, I want to use a specific number.
  3. For the rest, I need to multiply the previous result by a new number.

I’ve managed to do this manually for the first item, but I can’t figure out how to make it work for the whole list automatically. Any ideas on how to set this up? Maybe using IF statements or some other function?

Here’s a simple example of what I mean:

A   |  B
----+----
Cat | 5
Dog | 10
Fish| 3

So if Cat is the first in the list, it should be 5. Then Dog would be 5 * 10 = 50, and Fish would be 50 * 3 = 150.

I’d really appreciate any help or suggestions on how to make this work!

2

I’ve encountered a similar challenge in my work, and I found a neat workaround using the INDIRECT function combined with ROW. Here’s what worked for me:

=INDIRECT(“B1”)*PRODUCT(INDIRECT(“B2:B”&ROW()-1))

This formula assumes your data starts in row 1. Place it in C1 and drag down. It multiplies the first value (B1) by the product of all previous rows, updating as you go down the list.

The beauty of this approach is its flexibility. You can easily adjust the starting row by changing the cell references in the formula. It’s been a lifesaver for me when dealing with varying list lengths and dynamic data sets.

Just remember to format your result column as numbers to avoid any display issues. Let me know if you run into any snags!

3

For this kind of calculation, you can use a combination of IF and OFFSET functions. Here’s a formula that should work:

=IF(ROW(A1)=1, B1, B1*OFFSET(A1, -1, 1))

Put this in cell C1 and drag it down. The IF statement checks if it’s the first row. If so, it uses the value in B1. For other rows, it multiplies the current B value by the result from the cell above in column C.

This assumes your data starts in row 1. If it starts lower, adjust the ROW(A1) part accordingly. Also, make sure your number column (B) contains actual numbers, not text that looks like numbers.

Hope this helps solve your problem!

4

hey, i think i got a simple solution for ya. try this formula:

=PRODUCT($B$1:B1)

just pop that in C1 and drag it down. it’ll multiply all the numbers from B1 to the current row. should do exactly what you’re after without any fancy IF statements. lemme know if it works!