Conversion to value between 1 to 10 only!

[wilchong via OfficeKB.com]

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(D1>40,D20-40,IF(D1>30,D1-30,
IF(D1>20,D1-20,IF(D1>10,D1-10,D1))))】.

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

Many thanks,
Wilchong

 

[Bob Phillips]

=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]

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

=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

 

[Brad]

=MOD(A8-1,10)+1

 

[wilchong via 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

=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

 

[Brad]

=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

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

=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