Complicated lookup from a table with scales

[Ecel Dumbo]

Hi Friends,

If you could please attached spreadsheet and help advise a Vlookup formula. This involves breaking a cell value and apply different scales based on the table given.

Any help would be appreciated

 

[Nebu]

Hi:

I have gone through the excel file , the no of steps can be eliminated using conditions but your step calculation logic is not consistent across , it changes for step 6 is there is any business reason for this?

Thanks

 

[Ecel Dumbo]

Thnks Nebu. I checked and there is no change in step calculation as shown in the table. Step 6 rate is meant for any consumption over the addition of Steps 1-5

Also when i changed the Total number of 630,346.02, to see what changes are happening, so for example-120,000. It is giving me a negative dollar at Step 6

Customer AA
Usage Price
Total 630,346.02
Step 1 1232.88 $48.49
Step 2 1479.45 $34.55
Step 3 5671.23 $129.64
Step 4 73972.59 $1,677.70
Step 5 328931.52 $7,032.56
Step 6 219,058.35 $3,846.66
630,346.02

 

[Nebu]

Hi:

See the following if conditions in your original formula.

Till Step 5 you are taking the difference

'=(IF(C2-E2-F2-G2-H2>=I2,I2,IF((C2>SUM(E2:H2)),(C2-SUM(E2:H2)),0)))

In step 6 you are checking whether the value is greater or not

'=(IF(C2>SUM(E2:I2),((C2-SUM(E2:I2))),0))

This was the logical difference I was talking about.

Thanks

 

[Ecel Dumbo]

Hi Nebu, i think i am not expalaining things clearly here.When you get time, Could you please see attached file where i have put in my comments

Thanks

 

[NARAYANK991]

Hi ,

I think the inconsistency is in the first column !

K2 has a formula such as :

=MIN(E2,$C2)

L2 , M2 , N2 , O2 and P2 can all have the formula :

=MAX(0,MIN($C2-SUM($E2:E2),F2))

entered in L2 and copied across.

Narayan

 

[Ecel Dumbo]

thank you very much narayan

 

[David Evans]

As usual Narayan and Nebu have helped you on this challenge - In the past, when face with a tiered rate structure, I have created a User Defined Function to calculate it - if its something you'd be interested in, I'd be happy to modify it to your problem and post it - however the answer will be no different than you get using the current solutions!

 

[Ecel Dumbo]

Thank you David. Please see attached file. Managed to apply formula in the shaded column but this is not complete. Two issues

Can you suggest a formula for column AC- which is Step 6 price. this means if the usage in column C goes over and above (10,964*30), it will be charged at 1.756.

Also a forumla to say if there are no values in column C, there is no daily charge in column AD.

 

[Ecel Dumbo]

In addition to the above requests, i noticed that
The formula suggested worked with my previous example. But when i reduced the usage amount, the final result is not what i want.

The purpose of this excercise is to ascertain gas cost based on usage shown in column C of the attached spreadsheet. Gas usage is run through different scales
PLease see attached

 

[David Evans]

The logic of these tiered or banded schedules can become confusing in my experience, but I think i'm correctly understanding your definition of the problem, EXCEPT for the value of cell C23, which I think should be
13,709.589
- the cumulative total of each prior rate tier? Does anyone else see it that way?

 

[NARAYANK991]

Hi David ,

I am not able to find any value in C23 ; can you mention the workbook that you are referring to ?

Narayan

 

[David Evans]

Sorry Narayan -I've been out of touch for a day or so - will look at which file at was using an get back to you ....