multiple if statements

[vickiecroft]

In Column B I have a number with multiple digits. The first part of my formula I want to round up to the next whole number, then if the number is greater than or equal to 18 place the number 18 in the cell, if the number is less than or equal 6 place the number 6 in the cell, but if the number in the cell is 7 through 17 I want it to place the actual number in the cell.

 

[jtakw]

Hi,

When you say:

but if the number in the cell is 7 through 17 I want it to place the actual number in the cell

Do you mean Un-Rounded?

Book1
	B	C
1	5.5678	6
2	11.2457	11.2457
3	25.78965	18
Sheet292

Cell Formulas
Range	Formula
C1	=IF(ROUNDUP(B1,0)>=18,18,IF(ROUNDUP(B1,0)<=6,6,B1))

 

[dreid1011]

Perhaps something like this:

=MIN(18,MAX(ROUND(B2,0),6)) <-Except, I realize now this does not take into account the exact value if it falls within the range and will return the rounded value regardless.

Or

=IF(ROUND(B2,0)>=18,18,IF(ROUND(B2,0)<=6,6,B2))

 

[jtakw]

@ OP and dried,

We can also use the MEDIAN function:

Book1
	B	C	D
1	5.5678	6	6
2	11.2457	11.2457	11.2457
3	25.78965	18	18
Sheet292

Cell Formulas
Range	Formula
C1	=IF(ROUNDUP(B1,0)>=18,18,IF(ROUNDUP(B1,0)<=6,6,B1))
D1	=MEDIAN(18,B1,6)

 

[dreid1011]

Almost. My MIN/MAX would have worked if I could keep it simple and still return the exact value. Point in case, 17.5+ will return 18 with MIN/MAX, but 17.5+ with MEDIAN.

MEDIAN works great for the decimal values from 6.5-17.4 though. MIN/MAX will return the rounded results. At this time, I would like to petition Microsoft for a new function.. to be able to calculate on a rounded number but return original number if necessary. Let's call it ROUNDX() heh.

 

[jtakw]

Then the MEDIAN can work this way:

Book1
	B	C	D
1	5.5678	6	6
2	11.2457	11.2457	11.2457
3	25.78965	18	18
4	17.7	18	18
Sheet292

Cell Formulas
Range	Formula
C1	=IF(ROUNDUP(B1,0)>=18,18,IF(ROUNDUP(B1,0)<=6,6,B1))
D1	=MEDIAN(18,IF(ROUNDUP(B1,0)>=18,18,B1),6)