0

The discount in question is a fixed percentage, the numbers to be reduced by said percentage is in the column to the immediate left. I've tried this in D5:

=ARRAYFORMULA(C5:C27 - (INDIRECT("RC[-1]",FALSE) * TO_PURE_NUMBER(D4)))

Where C5:C27 are the original numbers and the indirect bit just grabs the cell to the left with D4 being the percentage.

My problem is it grabs the cell to the left.. of the first row only so every number gets discounted by the same exact amount.

I am using Google Sheets.

Improve this question
1

1 Answer 1

Reset to default
0

The indirect() function does not work the way you seem to expect in an arrayformula() wrapper. You may want to find another way to reference the cells "to the left". Try this pattern that uses simple range references:

=arrayformula(C5:C27 - B5:B27 * D4)

...or this pattern that uses offset():

=arrayformula(C5:C27 - offset(C5:C27, 0, -1) * D4)

Improve this answer
1
  • The first seems to be what I needed, at first it was subtracting the quantities from the prices in my sheet but once I switched it to the same range and fixed that it works perfectly. Oddly enough I didn't even need TO_PURE_NUMBER anymore for the percentage. I guess I got too tied up in the code examples I saw for ArrayFormula and didn't think you could use a range again after the first. I am not used to sheets or gscript, I might have been better off doing this in Python and just exporting a sheet. Thanks!
    – Anthony Forsberg
    Commented Aug 3, 2021 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.