Formula that splits cell contents and returns calc value

[Scott02]

Hello all,

I've stared at this formula way too long and can't find the error. I'd appreciated some fresh eyes to find the problem.

In a cell, I have a 15 digit value that, when broken into sections, represents different information. Here's the break down of parts:
Cell E2 = 200002000000000
Digits 2-5 = "0000" is % of Coin - expl 0360 = 3.6%
Digits 6-9 = "2000" is % of Revenue - expl 0500 = 5.0%
Digits 10-14 = "00000" is Daily Fee in $ - expl 07500 = $75
Digits 1 and 15 are not significant for my purpose. The formula I was using worked, until I added a MAX function to take the larger of either (a) % of Rev or (b) $35/Day for digits 6-9. Fees for some of the equipment we lease is either 20% of Rev or a mininum of $35/day used. Here's an example of the data, and the formula I'm currently struggling with.
A B C D E
1 Coin Revenue Days Code Correct Calc
2 80,000 12,000 30 200002000000000 2,400
3 80,000 5,000 30 200002000000000 1,050
4 100,000 7,500 30 300000000075000 2,250
5 150,000 25,000 30 104900000000000 7,350

=IF(VALUE(D2)=0,0,IF((RIGHT(LEFT(D2,5),4)/10000)>0,((RIGHT(LEFT(D2,5),4)/10000)*A2),IF((RIGHT(LEFT(D2,14),5))>0,((RIGHT(LEFT(D2,14),5))/100*C2),IF((RIGHT(LEFT(D2,9),4)/10000)>0,MAX((RIGHT(LEFT(D2,9),4))/10000*B2,C2*35),0))))

I know it's a mouthful, but if someone wouldn't mind glancing it over, I'd appreciate it. I can get it to work correctly for one calc type, but not another. I'll make a change, then it works for the second, but not the first. I haven't been able to get it to work for all combinations.

Thanks in advance for your assistance.

Scott

 

[SkipVought]

Hi,

It might be simpler to just parse the column using Data/Text to columns.

Also, if you insist on using an expression, try using the MID string function instead of combining Left & Right.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[ETID]

Could you use sperate columns for each value?

the heading in cell F1 "% of Coin"
the formula in cell F2 =mid(d2,2,4)*.36

the heading in cell G1 "% of Revenue"
the formula in cell G2 =mid(d2,6,4)*.5

...and so on

 

[Scott02]

I'll be glad to replace Left/Right with Mid, but I'd rather not parse out the data if I can avoid it. Of course, if I don't have a choice, I'll certainly try it. Thanks for the advice.

Scott

 

[SkipVought]

Also, code the INNER-MOST values first to test if that's what you want.

Then build out-ward, testing as you go.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[Scott02]

Thanks Skip and ETID. I went ahead and split the column to see how much extra time/space it would take since I have to do this monthly, and it wasn't too much of an inconvenience after all. A revised formula worked perfectly evaluating each separate piece of the data. I guess I can work with a few more columns. Thanks again for your replies.

Scott