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

  • Thread starter Thread starter Dave Piper
  • Start date Start date
D

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
 
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)
 
...
...
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.
Click to expand...

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}),"?")
 
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)
 
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
 
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
Click to expand...

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
 
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
Click to expand...


That last can be simplified to:

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


--ron
 
Ron Rosenfeld said:
Result is #NUM! if B2 is negative due to the CEILING function.
Click to expand...

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

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