Multiplication with multiple IF statements based on a range on Google Sheets

0
votes

I'm trying to create a dynamic formula, where if the value of a cell is within a range, it will multiply by a certain value from another cell, and then check the next cell and if matching a value multiply is by the value of another cell.

I have a base value on cell R2 which should be multiplied by a certain number based on the value of cells on column I and then multiplied by a certain number based on the values of column J.

I came up with the following:

=R2*ifs($I$2:I386,"<=0.85",$I$2:I386,">0.5")*R4,ifs($I$2:I386,"<=0.9",$I$2:I386,">0.85")*R5,ifs($I$2:I386,"<=0.95",$I$2:I386,">0.9")*R6,ifs($I$2:I386,"<=1",$I$2:I386,">0.95")*R7,ifs($I$2:I386,"<=1.05",$I$2:I386,">1")*R8,ifs($I$2:I386,"<=1.1",$I$2:I386,">1.05")*R9,ifs($I$2:I386,"<=1.15",$I$2:I386,">1.1")*R10,ifs($I$2:I386,"<=1.20",$I$2:I386,">1.15")*R11,ifs($I$2:I386,"<=1.25",$I$2:I386,">1.20")*R12*ifs($J$2:J386,"1")*R14,ifs($J$2:J386,"2")*R15,ifs($J$2:J386,"3")*R16,ifs($J$2:J386,"4")*R17,ifs($J$2:J386,"5")*R18,ifs($J$2:J386,"N/A")*R19

However, I still get an error, and got stuck. Not sure how to change it.

1
google-sheets
what is the expected value for IFS($I$2:I386,"<=0.85",$I$2:I386,">0.5") in your formula ? - p._phidot_
share a copy of your sheet - player0
This can be greatly condensed. But first, what happens if I2:I386 is blank, or <=0.5, or > 1.25? - Erik Tyler
@Kayan, I've kept your post up in a separate browser window until now, but I'm assuming you no longer need help with this issue, since you haven't replied to anyone here. - Erik Tyler

1 Answers

0
votes

try:

=ARRAYFORMULA(
 IF((I2:I386 <= 0.85)*(I2:I386 > 0.5),  R2*R4,
 IF((I2:I386 <= 0.9) *(I2:I386 > 0.85), R2*R5,
 IF((I2:I386 <= 0.95)*(I2:I386 > 0.9),  R2*R6,
 IF((I2:I386 <= 1)   *(I2:I386 > 0.95), R2*R7,
 IF((I2:I386 <= 1.05)*(I2:I386 > 1),    R2*R8,
 IF((I2:I386 <= 1.1) *(I2:I386 > 1.05), R2*R9,
 IF((I2:I386 <= 1.15)*(I2:I386 > 1.1),  R2*R10,
 IF((I2:I386 <= 1.2)*(I2:I386 > 1.15), R2*R11,
 IF((I2:I386 <= 1.25)*(I2:I386 > 1.2), R2*R12,
 IF(J2:J386 = 1,     R2*R14,
 IF(J2:J386 = 2,     R2*R15,
 IF(J2:J386 = 3,     R2*R16,
 IF(J2:J386 = 4,     R2*R17,
 IF(J2:J386 = 5,     R2*R18,
 IF(J2:J386 = "N/A", R2*R19, ))))))))))))))))

see: https://webapps.stackexchange.com/q/123729/186471