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

=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

Dear Bob,
thank for your suggestion! However, your suggested formula cannot convert 20
to 10, 30 to 10, 40 to 10 and etc!

How to revise your suggested formula in order to achieve those result?

Many thanks,
Wilchong


Bob Phillips wrote:
=D1-LOOKUP(D1,{10,20,30,40},{10,20,30,40})

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
[quoted text clipped - 11 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

=MOD(A8-1,10)+1

--
Wag more, bark less


"wilchong via OfficeKB.com" wrote:

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

Hello Brad,

Many thank for your suggestion, the Excel formula is working very well and
efficient! I have another related question, instead of only extract the 9
from 29, 8 from 38, How to revise your suggested formula in order to achieve
20 from 29, 30 from 38? This existing question is just opposite of my
original question.

Many thanks,
Wilchong



Brad wrote:
=MOD(A8-1,10)+1

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
[quoted text clipped - 9 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

=floor(b43,10)

Depending on how you get to this location - you may have opportunity to mark
that I have answered two of your questions. If you would be so kind as
"click the box" I would appreciate it....

--
Wag more, bark less


"wilchong via OfficeKB.com" wrote:

Hello Brad,

Many thank for your suggestion, the Excel formula is working very well and
efficient! I have another related question, instead of only extract the 9
from 29, 8 from 38, How to revise your suggested formula in order to achieve
20 from 29, 30 from 38? This existing question is just opposite of my
original question.

Many thanks,
Wilchong



Brad wrote:
=MOD(A8-1,10)+1

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
[quoted text clipped - 9 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1