"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)

1 - 15 of 15 Posts

"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)

Joined

·
4,406 Posts

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

Bob

Bob

=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.

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

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

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 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)

FYI, the formatting got screwed up, but the first column of numbers is the row numbers.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)

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.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 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.

1 - 15 of 15 Posts

Join the discussion

Contractor Talk - Professional Construction and Remodeling Forum

A forum community dedicated to professional construction and remodeling contractors. Come join the discussion about the industry, trades, safety, projects, finishing, tools, machinery, styles, scales, reviews, accessories, classifieds, and more!

Full Forum Listing
Explore Our Forums

Recommended Communities

Join now to ask and comment!