 # Calculate Single Digits in Long Number

• I have a number code up to 6 digits long. If there are less than 3 digits(blank,1 or 2) I want to return 9999.

I want to multiply the last digit by 5,the second last digit by 2.5 and third last digit by 1.

I then want to add these 3 together.

I can manage most of this,but anywhere an zero occurs,I want to regard it as ten for computation purposes.

I attach a sheet with the result I require in col c,working on string in col b.

If possible I would like to do all of this with 1 formula(solution in 1 column)

## Files

• Re: Calculate From Parts Of A Number String

Try this

=IF(OR(A1="",LEN(A1)<3),9999,SUM(IF(RIGHT(A1)="0",10,RIGHT(A1))*5,IF(MID(A1,LEN(A1)-2,1)="0",10,MID(A1,LEN(A1)-2,1))*2.5,IF(MID(A1,LEN(A1)-3,1)="0",10,MID(A1,LEN(A1)-3,1))))

• Re: Calculate Single Digits in Long Number

Dave

Thanks--your solution provided some correct rows but not others.

I have enclosed amended sheet with your numbers.

Just to explain the first 3 rows manually.

(5x2)+(2.5x10)+(1x10) =85

(5x10)+(2.5x10) +(1x5)=80 ----your formula gets 72.5
(5X10)+ (2.5x10)+(1x6) =81

Every digit zero becomes a ten and is then calculated on as a ten.

Pytelium

## Files

• Re: Calculate Single Digits in Long Number

I expect Dave is asleep now, so I have fixed it:

=IF(OR(B2="",LEN(B2)<3),9999,SUM(IF(RIGHT(B2)="0",10,RIGHT(B2))*5,IF(MID(B2,LEN(B2)-1,1)="0",10,MID(B2,LEN(B2)-1,1))*2.5,IF(MID(B2,LEN(B2)-2,1)="0",10,MID(B2,LEN(B2)-2,1))))

• Re: Calculate Single Digits in Long Number

Try:

=IF(LEN(B2)<3,9999,(MOD(RIGHT(B2)-10,-10)+10)*5+(MOD(LEFT(RIGHT(B2,2))-10,-10)+10)*2.5+(MOD(LEFT(RIGHT(B2,3))-10,-10)+10))

TJ

• Re: Calculate Single Digits in Long Number

TJ, Some nice tricks in your formula! • Re: Calculate Single Digits in Long Number

Thank you all 3. Both solutions work perfectly!

• Re: Calculate Single Digits in Long Number

Quote from tinyjack

Try:

=IF(LEN(B2)<3,9999,(MOD(RIGHT(B2)-10,-10)+10)*5+(MOD(LEFT(RIGHT(B2,2))-10,-10)+10)*2.5+(MOD(LEFT(RIGHT(B2,3))-10,-10)+10))

TJ

Nice one! Here's a variation...

=IF(LEN(B2)<3,9999,SUM((MOD(MID(RIGHT(B2,3),{1,2,3},1),-10)+10)*{1,2.5,5}))

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!