Greater Than formula

[ginasucasa]

I'm in need of the following:
a1=9

Formula: If a1 is greater than 8, I need b1 to show 8 only. If it is less
than 8, I need it to show the amount that is less than 8.

So b1 on this should show 8.

Thanks!

 

[Chip Pearson]

You can do this with the MIN function. In cell B1, enter

=MIN(A1,8)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[ginasucasa]

Thank you. It works for over 8, but:
if a1=7 how do I get b1=7 with the other formula? I want to be able to use
this formula throughout a worksheet.

 

[David Biddulph]

So what does the formula give for you when a1=7?

 

[Tyro]

Did you change A1 to 7??? You would see that the formula produces 7 as an
answer.

Tyro

 

[Chip Pearson]

The formula =MIN(A1,8) will return the lesser of A1 and 8 to the cell that
contains the formula. If A1 is 7, then the formula returns 7. A formula
cannot change the value of another cell -- it can only return a value to the
cell that contains it, nothing more. That is, you cannot enter a formula in
B1 that changes the value in C1. There is no way to create a "reusable"
formula to be used in multiple cells. Each cell that needs the result must
contain the formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)