Help With Excel Formula

Christine

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)

Glasshousebltr

I think it's (a1..d1), or maybe that just works on adding.

Bob

__________________
Bob

PipeGuy

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

to return a text message instead of the value 0

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

jproffer

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.

PipeGuy

Your right - I stand corrected. After reading his question again it's clear that I didn't get it. Good call.

jproffer

You were right too....as long as 0 or 4 is the only possible numbers...it'll work fine... ...just another way to do the same thing

Christine

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 A11? 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

Christine

What's up with the grin face in my formula?! A11

Christine

AAARRGH! Go away, smiley face!!!!!!!!!!! A1:d1

jproffer

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.

jproffer

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.

Christine

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)

Christine

FYI, the formatting got screwed up, but the first column of numbers is the row numbers.

jproffer

myrange is the defined range that you would need to setup...

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

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.

Christine

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