Solved

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

Posted on 2015-02-23
7
53 Views
Last Modified: 2015-03-06
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
Comment
Question by:shampouya
  • 3
  • 2
  • 2
7 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
Comment Utility
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
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
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
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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

by:Norie
Comment Utility
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

by:byundt
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now