Solved

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

Posted on 2015-02-23
7
59 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 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 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
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!

 
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

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!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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