[Solved] Multiple formulas / priority / override?

[swdistro]

I'm not sure if this is possible, but I currently have my spreadsheet set up for certain cells to duplicate the contents of the cell above it i.e. "=INDIRECT(ADDRESS(ROW()-1;COLUMN();1))", but I would also like to add a function where if the E column contains "2" then display "NO-TAX", and override the "=INDIRECT(ADDRESS(ROW()-1;COLUMN();1))" function, and if it contains anything else to just continue with the =INDIRECT function as usual.

Is this somehow doable? Thanks

[Villeroy]

https://wiki.openoffice.org/wiki/Docume ... X_function
https://wiki.openoffice.org/wiki/Docume ... H_function
https://wiki.openoffice.org/wiki/Docume ... T_function

and of course you know everything about[Tutorial] Absolute, relative and mixed references

[MrProgrammer]

=INDIRECT(ADDRESS(ROW()-1;COLUMN();1))

Using that complicated formula suggests that you do not understand relative references, which is one of the most fundamental ideas in a spreadsheet. It will be difficult to use a spreadsheet effectively until you learn about this. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.

I would also like to add a function where if the E column contains "2" then display "NO-TAX", and override the [other] function

Yes, of course. You can use the IF() function. If you need any additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[swdistro]

=INDIRECT(ADDRESS(ROW()-1;COLUMN();1))

Using that formula suggests that you do not understand relative references, which is one of the most fundamental ideas in a spreadsheet. It will be difficult to use a spreadsheet effectively until you learn about this. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.

Well, it's true. I got that formula from my last forum post on here a few months ago.

I'll attach my document shortly.

[RusselB]

You can always amend your formula so that it becomes part of an IF function, similar to how you get NO TAX to display when the entry in column E is a 2

[swdistro]

Okay, here is a different approach that suits the situation better:

I need:

IF K2 equals one of the following:

BC
AB
SK
MB
ON
QC
NB
NS
NL
PEI
NVT
YT
NWT

Then display whatever of those is in K2.

If it is anything else, display NO-TAX.

AND as a priority over all of that, if E2 equals 2 (not contains, as there are other larger numbers that might have 2 in it, it needs to be "2" and "2' alone), then also NO-TAX, even if K2 contains any of the above letters.

[FJCC]

I think this formula meets your needs. I stored the 13 abbreviations in the cell range M1:M13. You can put them anywhere that is convenient and edit the formula accordingly.

Code: Select all

=IF(OR(ISNA(MATCH(K2;$M$1:$M$13;0));E2 = 2);"NO-TAX";K2)

Explaining that from the inside out:
MATCH() looks for the content of K2 in the range M1:M13. If it finds it, it returns a number. If it does not find it, it returns #N/A. The ISNA() returns TRUE or FALSE depending on what MATCH did. TRUE means MATCH did not find the text. The OR() checks whether ISNA is TRUE or E2 = 2. If either of those is TRUE, OR returns TRUE. If the OR is TRUE, the IF() returns NO-TAX, otherwise, it returns whatever is in K2.

[swdistro]

I think this formula meets your needs. I stored the 13 abbreviations in the cell range M1:M13. You can put them anywhere that is convenient and edit the formula accordingly.

Code: Select all

=IF(OR(ISNA(MATCH(K2;$M$1:$M$13;0));E2 = 2);"NO-TAX";K2)

Explaining that from the inside out:
MATCH() looks for the content of K2 in the range M1:M13. If it finds it, it returns a number. If it does not find it, it returns #N/A. The ISNA() returns TRUE or FALSE depending on what MATCH did. TRUE means MATCH did not find the text. The OR() checks whether ISNA is TRUE or E2 = 2. If either of those is TRUE, OR returns TRUE. If the OR is TRUE, the IF() returns NO-TAX, otherwise, it returns whatever is in K2.

That works perfectly, thank you.

If there is a way that it can be done without needing that extra range (e.g. your M1:M13) that would be ideal, but if not I can absolutely work with this.

[FJCC]

This also works, if you strongly want to avoid using an external range.

Code: Select all

=IF(OR(ISNA(MATCH(K2;{"BC";"AB";"SK";"MB";"ON";"QC";"NB";"NS";"NL";"PEI";"NVT";"YT";"NWT"};0));E2 = 2);"NO-TAX";K2)

If you ever want to adjust the list and the formula is in several cells, it will be painful and prone to error.

[swdistro]

This also works, if you strongly want to avoid using an external range.

Code: Select all

=IF(OR(ISNA(MATCH(K2;{"BC";"AB";"SK";"MB";"ON";"QC";"NB";"NS";"NL";"PEI";"NVT";"YT";"NWT"};0));E2 = 2);"NO-TAX";K2)

If you ever want to adjust the list and the formula is in several cells, it will be painful and prone to error.

That'll do!

The formula will only ever be in the Q column rage, the list is just the provinces of Canada, and the only thing that will force NO-TAX other than a non-Canadian province will be "2" in E2, so this should work out in perpetuity. Any changes I can then just drop into Q2 and drag through the length of the column.

I'd consider this now solved. Thank you very much!

[FJCC]

What if Canada annexes Minnesota?