Solved

# IS there a more compact way of writing an OR formula in Excel 2010?

Posted on 2015-02-23
53 Views
Is there a more compact way of writing the formula below? I would think that writing "Total Due" 3 times shouldn't be necessary. This is not VBA, it's just a formula in one of my cells.

``````=IF(OR(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="Total Due",INDIRECT(ADDRESS(ROW()-2,COLUMN()))="Total Due",INDIRECT(ADDRESS(ROW()-3,COLUMN()))="Total Due"),I17+L17+N17,"")
``````
0
Question by:shampouya
• 3
• 2
• 2

LVL 33

Accepted Solution

Norie earned 500 total points
You could use a different function.

For example, MATCH.

0

LVL 59

Expert Comment

Like Norie Said..You can use different function...

if you like a shorter one version then you can use countif with offset like this...

Saurabh...
0

LVL 33

Expert Comment

Saurabh

I was going to use OFFSET but I couldn't quite figure how it would fit with the ADDRESS and INDIRECT.

I can see how to do it now.

0

LVL 59

Expert Comment

Glad to be of help buddy..and after reading your formula and rechecking what he was trying to do..realized he want to check 3 rows not 4 rows..so this will become...

Saurabh...
0

LVL 80

Expert Comment

I realize that the question has been answered, but I feel uncomfortable whenever the ADDRESS function is part of the answer. There is invariably a better way.

For example, if the formula is going in cell E13, you could use:
=IF(COUNTIF(E10:E12,"Total Due")>0,I17+L17+N17,"")

If you are installing the formula using VBA, I suggest using RC notation to avoid the need to know the address of the target cell:
=IF(COUNTIF(R[-3]C:R[-1]C,"Total Due")>0,R17C9+R17C12+R17C14,"")
If the above formula is put in cell E13 using the .FormulaR1C1 property of that range, you would get:
=IF(COUNTIF(E10:E12,"Total Due")>0,\$I\$17,\$L\$17+\$N\$17,"")
0

LVL 33

Expert Comment

Byundt

I agree with you about using ADDRESS but since we didn't know which cell the formula was to go in and VBA was ruled out I'm not sure there's another way.
0

LVL 80

Expert Comment

Norie,
=IF(COUNTIF(INDEX(\$1:\$1048576,ROW()-3,COLUMN()):INDEX(\$1:\$1048576,ROW()-1,COLUMN()),"Total Due")>0,I17+L17+N17,"")

0

## Featured Post

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.