=D1-LOOKUP(D1,{10,20,30,40},{10,20,30,40})

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:845e202b7bea2@uwe...
I have a problem for an excel formula. In my example, there is one value in
cell D1: 15, and I want an excel formula which can convert it to 5. The
objective of the excel formula is to convert 45 to 5, 10 to 10, 20 to 10,
30
to 10, 39 to 9 etc.

I know my following excel function can do it
?IF(D140,D20-40,IF(D130,D1-30,
IF(D120,D1-20,IF(D110,D1-10,D1))))?.

My question is that is there any extremly simple excel formula which can
convert those value immediately?

Many thanks,
Wilchong

--
Message posted via http://www.officekb.com