Contractor Talk - Professional Construction and Remodeling Forum banner
1 - 15 of 15 Posts

·
Registered
Joined
·
58 Posts
Discussion Starter · #1 ·
Hi, all. Can anyone tell me how to put the following phrase into a formula for Excel?

"If any cell in range A1 through D1 is equal to or greater than 4, return the value (not text) 45."

I can't find anything to tell me how to formulize the "any cell in range..." part.

Thanks in advance!

-Christine (AKA Hollis)
 

·
Registered
Joined
·
3,265 Posts
=if(sum(a85:d85)>=4,45,0)

to return a text message instead of the value 0

=if(sum(a85:d85)>=4,45,"message")
 

·
Repair/Remodeling Tech.
Joined
·
1,645 Posts
It depends on what exactly you mean...if a1 - d1 will be either 0 or 4..and nothing else...EVER, then pipe's way would work. What if a1=2, d1=2, c1=1, d1=0...none of them fit your criteria, but the sum is more than 4 so 45 would return.

=IF(OR(A1>=4,B1>=4,C1>=4,D1>=4),45,"")

That would work if you want to return 45 if ANY of the cells are equal to or more than 4.

=IF(AND(A1>=4,B1>=4,C1>=4,D1>=4),45,"")

That would do the job if you want to return 45 only when ALL of the 4 cells are 4 or more.
 

·
Registered
Joined
·
3,265 Posts
jproffer said:
It depends on what exactly you mean
Your right - I stand corrected. After reading his question again it's clear that I didn't get it. Good call.
 

·
Repair/Remodeling Tech.
Joined
·
1,645 Posts
You were right too....as long as 0 or 4 is the only possible numbers...it'll work fine... :cheesygri ...just another way to do the same thing
 

·
Registered
Joined
·
58 Posts
Discussion Starter · #7 ·
Okay, guys. JProffer's formula with the OR works. It will look at all the designated cells and see if ANY ONE of them is equal to or greater than 4. There only needs to be one cell that meets that criteria. Thanks for the help.

Now, any chance I can change the A1,B1,C1,D1 in the formula into a range? Such as A1:D1? I'm going to tinker a bit, but If I could do this then anytime the row gets modified with more/less columns, the equation's range will adjust accordingly.

-Christine
 

·
Repair/Remodeling Tech.
Joined
·
1,645 Posts
Go to: Insert/Name/Define...then at the bottom, in "refers to:" type $A$1:$D$1. Now at the very top name it "myrange" or whatever you want. If you insert a column between A&B or B&C or C&D, the definition will update accordingly, but that's the key you MUST insert any new columns you want included in your range in one of these areas.

EDIT...Kicker is, the definition won't work in your IF statement (I don't think, I'll play with it more when I get some time). We can come up with a workaround I'm sure.
 

·
Repair/Remodeling Tech.
Joined
·
1,645 Posts
OK, this has only been moderately tested so make sure it works, but:

=IF(SUMIF(myrange,">=4")>=4,45,"")

Put that in place of your "IF" statement after you set your defined range. Let me know if you have any trouble.
 

·
Registered
Joined
·
58 Posts
Discussion Starter · #12 ·
jproffer said:
OK, this has only been moderately tested so make sure it works, but:

=IF(SUMIF(myrange,">=4")>=4,45,"")

Put that in place of your "IF" statement after you set your defined range. Let me know if you have any trouble.

I don't think this is going to work, nor will any "myrange" because I have rows and rows of data. For instance, my sheet may look like this:

A B C D E

1 3 3 3 3 45 (since no cell in this row is > 4, E1 returns 45)
2 6 2 1 2 91 (since A2 in this row is > 4, E2 returns 91)
3 8 8 8 8 91
4 3 2 1 0 45

Total = 272 (I want to then total E1 through E4, so 45 and 91
must be values)
 

·
Registered
Joined
·
58 Posts
Discussion Starter · #13 ·
Hollis said:
I don't think this is going to work, nor will any "myrange" because I have rows and rows of data. For instance, my sheet may look like this:

A B C D E

1 3 3 3 3 45 (since no cell in this row is > 4, E1 returns 45)
2 6 2 1 2 91 (since A2 in this row is > 4, E2 returns 91)
3 8 8 8 8 91
4 3 2 1 0 45

Total = 272 (I want to then total E1 through E4, so 45 and 91
must be values)
FYI, the formatting got screwed up, but the first column of numbers is the row numbers.
 

·
Repair/Remodeling Tech.
Joined
·
1,645 Posts
myrange is the defined range that you would need to setup...

Go to: Insert/Name/Define...then at the bottom, in "refers to:" type $A$1:$D$1. Now at the very top name it "myrange" or whatever you want.
You don't have to name it "myrange", you can name it anything, then in your formula you would put whatever name you choose in place of "myrange" in the last formula.

If you can't get it, I can set it up for you and email it to ya :cheesygri

EDIT: BTW, the 45 would be a value. Is that the only 4 rows you have like this? If so I'll set that up and see if I can post it as an attachment here.
 

·
Registered
Joined
·
58 Posts
Discussion Starter · #15 ·
Hey, all. I've been diverted from attending to my excel issue for the past several days and I'll be going out of town for the next week. Will try to follow up on this problem when I return. Thanks for all your efforts! Have a great week. -Christine
 
1 - 15 of 15 Posts
Top