If then, within range, greater than, etc. help

[Dave Piper]

Hello,

Thanks in advance for your help.

I need some newbie help...

I'm looking for a function that will perform the following:

In cell C2, check the value of B2 and produce a corresponding number.

So if the value in B2 is less than or equal to 24 then return 0, if
greater than 24 and less than or equal to 48 then return 1, if greater
than 48 and less than or equal to 72 then return 2, if greater than 72
and less than or equal to 96 then return 3, if greater than 96 and less
than or equal to 120 then return 4, and lastly if greater than 120 and
less than or equal to 144, then return 5.

Thanks again,

DP

 

[J.E. McGimpsey]

one way:

=IF(B2<=144,MAX(0,CEILING(B2,24)/24-1),"Too big")

 

[Bob Phillips]

Dave,

=if(B2<=144,VLOOKUP(B2,{0,0;25,1;49,2;73,3;97,4;121,5},2),"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

...
...

So if the value in B2 is less than or equal to 24 then return 0, if
greater than 24 and less than or equal to 48 then return 1, if greater
than 48 and less than or equal to 72 then return 2, if greater than 72
and less than or equal to 96 then return 3, if greater than 96 and less
than or equal to 120 then return 4, and lastly if greater than 120 and
less than or equal to 144, then return 5.

Brute force:
C2:
=IF(B2<=24,0,IF(B2<=48,1,IF(B2<=72,2,IF(B2<=96,3,IF(B2<=120,4,IF(B2<=144,5,
"?"))))))

Problem-specific:
C2:
=IF(B2<=144,MAX(0,CEILING(B2/24,1)-1),"?")

Table-driven:
C2:
=IF(B2<=144,LOOKUP(144-B2,{0,24,48,72,96,120},{5,4,3,2,1,0}),"?")

 

[Harlan Grove]

=if(B2<=144,VLOOKUP(B2,{0,0;25,1;49,2;73,3;97,4;121,5},2),"")

...

Assumes B2 would always be an integer.

 

[J.E. McGimpsey]

Or, a bit shorter:

=if(B2<=144,LOOKUP(B2,{0,0;25,1;49,2;73,3;97,4;121,5}),"")

NOTE: this fails if B2 can be a value between 24 and 25, 48 and 49,
etc. In that case, use an arbitrarily small "delta" (e.g.,
24.0000000000001, 49.0000000000001, etc)

 

[Anders S]

Dave.

Try

=INT((B2-1)/24)

HTH
Anders Silven

 

[Anders S]

Addition to cover zero and negative values:

=IF(B2<=24,0,INT((B2-1)/24))

HTH
Anders Silven


"Anders S" <[email protected]> skrev i meddelandet Dave.

Try

=INT((B2-1)/24)

HTH
Anders Silven

 

[Ron Rosenfeld]

Hello,

Thanks in advance for your help.

I need some newbie help...

I'm looking for a function that will perform the following:

In cell C2, check the value of B2 and produce a corresponding number.

So if the value in B2 is less than or equal to 24 then return 0, if
greater than 24 and less than or equal to 48 then return 1, if greater
than 48 and less than or equal to 72 then return 2, if greater than 72
and less than or equal to 96 then return 3, if greater than 96 and less
than or equal to 120 then return 4, and lastly if greater than 120 and
less than or equal to 144, then return 5.

Thanks again,

DP

You don't specify what you wish to happen if the number is greater than 144.

=MAX(0,MIN(5,ROUNDUP(B2/24,0)-1))

will return 5 for any number greater than 120, but otherwise meets your
requirements.

Depending on what you want:

=IF(B2>144,"Too Big",MAX(0,MIN(5,ROUNDUP(B2/24,0)-1)))



--ron

 

[Ron Rosenfeld]

You don't specify what you wish to happen if the number is greater than 144.

=MAX(0,MIN(5,ROUNDUP(B2/24,0)-1))

will return 5 for any number greater than 120, but otherwise meets your
requirements.

Depending on what you want:

=IF(B2>144,"Too Big",MAX(0,MIN(5,ROUNDUP(B2/24,0)-1)))



--ron

That last can be simplified to:

=IF(B2>144,"Too Big",MAX(0,ROUNDUP(B2/24,0)-1))


--ron

 

[Ron Rosenfeld]

Problem-specific:
C2:
=IF(B2<=144,MAX(0,CEILING(B2/24,1)-1),"?")

Result is #NUM! if B2 is negative due to the CEILING function.


--ron

 

[Harlan Grove]

Result is #NUM! if B2 is negative due to the CEILING function.

Didn't realize that about CEILING. So change it to

=IF(B2<=144,CEILING(MAX(1,B2/24),1)-1),"?")

 

[Ron Rosenfeld]

Didn't realize that about CEILING. So change it to

=IF(B2<=144,CEILING(MAX(1,B2/24),1)-1),"?")

Or use the ROUNDUP function.


--ron