Greater Than Less Than

[Beachfan]

Hi all, I am needing cell I31 to look at cell G28 and see if it is greater than 80 but less than 86 and if so by how many. I have been racking my brain trying to figure this one out and any help would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
It's not entirely clear what you are after but maybe

=IF(AND(G28>=80,G28<=86),G28-80,"??")

You didn't say what should happen if G28 is outside the range.

 

[Joe4]

Welcome to the Board!

Is this what you are looking for?

=IF(AND(G28>80,G28<86),86-G28,"")

 

[Toadstool]

Hi Beachfan,

You don't show column/row and you don't say what to do if the number is not in the range so I hope this helps:

Beachfan.xlsx
	F	G
3	84	2
4	93
5	2
6	81	5
Sheet1

Cell Formulas
Range	Formula
G3:G6	G3	=IF(AND(F3>80,F3<86),86-F3,"")

 

[Joe4]

Note that your question was a little ambiguous, and Fluff and I interpretted differently:

I am needing cell I31 to look at cell G28 and see if it is greater than 80 but less than 86 and if so by how many

"by how many"
- Fluff interpretted as how many more than 80
- I interpretted it as how many less than 86

It isn't obvious from the way you worded the question, but you have a solution for both possibilities.

 

[Beachfan]

Sorry if I was unclear. If cell G28 is over 80 but under 86 I need to know by how much, but not to go over 86. If it is under 80 then do nothing. Does that help more?

 

[Fluff]

Does that help more?

Fraid not.
Can you post some examples showing expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

Sorry if I was unclear. If cell G28 is over 80 but under 86 I need to know by how much, but not to go over 86. If it is under 80 then do nothing. Does that help more?

I think Fluff's original answer should give you that. Just replace the "??" with "".

=IF(AND(G28>=80,G28<=86),G28-80,"")

 

[Beachfan]

Being on a government computer system I am not able to download anything so that would not be an option. sorry, when i plugged that in it just gave me a blank even though G28 is over 80 and less than 86

 

[Joe4]

G28 is 93, which does not meet the criteria you gave of being between 80 and 86.
You said that if it was not between 80 and 86, then do nothing.

Did you really mean anything greater than 80, but cap it off at 6 (86-80)?
If so, try this:

=IF(G28>80,MIN(G28-80,6),"")