Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-23
7
Medium Priority
?
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 2000 total points
ID: 40626326
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
ID: 40626794
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 34

Expert Comment

by:Norie
ID: 40626810
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 40626823
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 81

Expert Comment

by:byundt
ID: 40648561
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 34

Expert Comment

by:Norie
ID: 40648678
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 81

Expert Comment

by:byundt
ID: 40649099
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

704 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