Min / Max of a cell

[used2reg]

I have a an excel sheet where I need to get the MIN/MAX of a cell and refrence it to another cell

I have "3" in a cell and when i Reference this cell i would like for it to return a 3, but if i have a 8 in the cell I would like to return a 4


So lets say (the A1 and A2 cells are a calculated field)

A1 = 3
A2 = 8

I would like to see

C1 = 3
C2 - 4



hope i left enough details..

 

[Fluff]

What is the logic behind what you are trying to do?

 

[JimM]

Assuming 4 is always the highest number you want

In cell C1 use the formula
=MIN(A1,4)
and copy down

 

[used2reg]

What is the logic behind what you are trying to do?

I am creating a sheet for billing; we charge by the # of people (A1, A2) that attend; but can only bill for 4 (Max); but need to charge 3 when only 3 attend

 

[used2reg]

Assuming 4 is always the highest number you want

In cell C1 use the formula
=MIN(A1,4)
and copy down

When i have a 3 in the field it enters 4 for the C value (can only have 3)

 

[Fluff]

Check that your numbers are proper numbers, as that does work

+Fluff 1.xlsm
	A	B	C
1
2	1		1
3	2		2
4	3		3
5	4		4
6	5		4
Data

Cell Formulas
Range	Formula
C2:C6	C2	=MIN(A2,4)

 

[JimM]

Is the 'field' formatted as a text?

See if this works

=MIN(--A1,4)

 

[used2reg]

Check that your numbers are proper numbers, as that does work

+Fluff 1.xlsm
	A	B	C
1
2	1		1
3	2		2
4	3		3
5	4		4
6	5		4
Data

Cell Formulas
Range	Formula
C2:C6	C2	=MIN(A2,4)

Thanks for your help ... fields set as text

 

[Fluff]

Glad we could help & thanks for the feedback.