Google Sheets Conditional Formula

0
votes

I am in need of a different formula for the same column cells depending on the values of another column.

I am trying to avoid writing a five-line formula concatenating all of them into one because of readability and maintainability. It's quite possible for the formulas to be added, deleted and updated in future.

here is a simplified example:

enter image description here

the formula I use for deductions right now is:

=IF(E7 = "salary", D7 * 0.2, IF(E7 = "rent", D7 * 0.15, D7)

Ideally, I would like to somehow call a separate formula in this IF statement instead of doing the calculations in place. The separate formulas, in this case, being D7 * 0.15 and D7 * 0.2 but in my case are more complicated.

I can code JavaScript and I've read how to create custom functions that could do that (in js) but think there would be a more spreadsheedian way.

So, is there a way to call separate formulas inside a formula and if not what is the best practice for this kind of situations.

1
google-sheetsspreadsheetgoogle-sheets-formula
so whats your question exactly? – player0
I edited my initial post. thanks @player0 – Stamoulohta

1 Answers

0
votes

I think the easiest way to do this is by using a switch:

=SWITCH(B2:B5, "Salary", A2:A5*0.2, "Rent", A2:A5*0.5)

In this example "Type" is column B and "Amount" is column A. You can change the range as you see fit and add in more conditions/types too.