• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • Last Modified:

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

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,"")

Open in new window

0
shampouya
Asked:
shampouya
  • 3
  • 2
  • 2
1 Solution
 
NorieCommented:
You could use a different function.

For example, MATCH.

=IF(ISNUMBER(MATCH("Total Due",INDIRECT(ADDRESS(ROW()-3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())), 0)),I17+L17+N17,"")
0
 
Saurabh Singh TeotiaCommented:
Like Norie Said..You can use different function...

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

=IF(COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-4,0,3,1),"Total Due")>0,I17+L17+N17,"")

Saurabh...
0
 
NorieCommented:
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.

=IF(ISNUMBER(MATCH("Total Due",OFFSET(INDIRECT(ADDRESS(ROW()-3,COLUMN())),0,0,3), 0)),I17+L17+N17,"")
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saurabh Singh TeotiaCommented:
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...

=IF(COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-3,0,3,1),"Total Due")>0,I17+L17+N17,"")

Saurabh...
0
 
byundtCommented:
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
 
NorieCommented:
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
 
byundtCommented:
Norie,
Of course there is another way, though I'll admit your INDIRECT & ADDRESS formula is shorter:
=IF(COUNTIF(INDEX($1:$1048576,ROW()-3,COLUMN()):INDEX($1:$1048576,ROW()-1,COLUMN()),"Total Due")>0,I17+L17+N17,"")

Brad
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now