Solved

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

Posted on 2015-02-23
7
56 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
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 33

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 33

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

785 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