What should be an ideal Formula to fulfill the condition ?

[RapchikM]

Hello

What should be ideal formula to get desired answer ?

Range of Units	Units Consumed	Rate	Amt
Col C	Col D	Col E	Col F
000-100 Units	100	1.65	165
000-100 Units	200	4.20	840
301-500 Units	200	7.65	1530
Above 500 Units	500	8.35	3825

Cell B9 = 510 ie Total Units Consumed
Cell F9 contains following Formula . =(VLOOKUP(B9,$D$3:$F$6,3)+(B9)-(LOOKUP(B9,$D$3:$F$6)))*VLOOKUP(B9,D$3:F$6,2)
Which gave answer as 4258.50
instead of 2618.50

basically it goes if units Consumed
Less than 500 then 100 X 1.65 = 165.00
200 X 4.20 = 840.00
200 X 7.65 = 1530.00
10 X 8.35 = 83.50 I dont know what condition is required here Tried many things and it got messed up and i am confused
TOTAL 2618.50

I tried putting the Following formula too

=IF(ISNA(VLOOKUP(B9,$D$3:$F$6,3,FALSE))=TRUE,F3,VLOOKUP(B9,$D$3:$F$6,3,FALSE))+IF(ISNA(VLOOKUP(B9,$D$3:$F$6,3,FALSE))=TRUE,F4,VLOOKUP(B9,$D$3:$F$6,3,FALSE))+IF(ISNA(VLOOKUP(B9,$D$3:$F$6,3,FALSE))=TRUE,F5,VLOOKUP(B9,$D$3:$F$6,3,FALSE))+IF(ISNA(VLOOKUP(B9,$D$3:$F$6,3,FALSE))=TRUE,F6,VLOOKUP(B9,$D$3:$F$6,FALSE))

The answer came up was as 6360

Require your help to correct it
Thanks in advance

RapchikM

 

[Muhammad_Usman]

A bit lengthy but wroking

Receip.xlsm
	A	B	C	D	E	F	G	H	I	J
1	000-100 Units	100	1.65	165		Units	Bill
2	100-200 Units	200	4.2	840		500	2,535.00
3	301-500 Units	200	7.65	1530		200	585.00
4	Above 500 Units	500	8.35	3825		510	2,618.50
5						600	3,370.00
6						750	4,622.50
7
8
9
Sheet19

Cell Formulas
Range	Formula
G2:G6	G2	=IF(F2<=100,100*1.65,IF(F2<=200,((100*1.65)+(F2-100)*4.2),IF(F2<=500,((100*1.65)+(200*4.2)+((F2-300)*7.65)),((100*1.65)+(200*4.2)+(200*7.65)+((F2-500)*8.35)))))

 

[RapchikM]

Muhammad Usman

Range	Formula
Cell Formulas
G2:G6	G2	=IF(F2<=100,100*1.65,IF(F2<=200,((100*1.65)+(F2-100)*4.2),IF(F2<=500,((100*1.65)+(200*4.2)+((F2-300)*7.65)),((100*1.65)+(200*4.2)+(200*7.65)+((F2-500)*8.35)))))

Thanks very much for deriving the correct result and applying on different Units and Getting Amt Values.

What i don't get is using your formula How Can someone fix a defined Value for eg.

+(F2-100)*4.2) +((F2-300)*7.65)) +((F2-500)*8.35)

ie defining 100, 300 and 500. Following value 300 is not shown in column B. I was much determined to get the values from Column itself

Will appreciate your explanation
RapchikM

 

[Alex Blakenburg]

If you are prepared to change your lookup table set up, then this might work for you.

Book2
	A	B	C	D	E	F	G	H	I
1	Range	From	To	Base Amt	Rate	Incremental Max (optional)		Units	Bill
2	000-100 Units	0	100	0	1.65	165		500	2,535.00
3	100-200 Units	100	300	165	4.2	840		200	585.00
4	301-500 Units	300	500	1,005	7.65	1530		510	2,618.50
5	Above 500 Units	500		2,535	8.35			600	3,370.00
6								750	4,622.50
Data

Cell Formulas
Range	Formula
F2:F4	F2	=(C2-B2)*E2
B2:B5	B2	=N(C1)
D3:D5	D3	=(C2-B2)*E2+D2
I2:I6	I2	=VLOOKUP(H2,$B$2:$E$5,3,TRUE)+VLOOKUP(H2,$B$2:$E$5,4,TRUE)*(H2-VLOOKUP(H2,$B$2:$E$5,1,TRUE))

 

[RapchikM]

Dear Alex Sir,

Very much appreciated. This gives very nice clarity and easy break up.

BTW what does in B2 Cell indicate as =N(C1)

Thanks and Regards
RapchikM

 

[Alex Blakenburg]

Thanks for letting us know, Glad we coule help.
In terms of =N(C1), the N function turns text to 0 and for numbers returns the number.
I wasn't terribly consistent since I was just trying to set up the sample table but the theory is that when you have a table it is preferable to have the same formula in "all" the rows.
Unfortunately when you are referencing the previous row, row 1 is referring to the heading which is usually text. By using the N function the first data row handles the fact that the previous row was the heading text and gave the 0 as the starting amount which is what the sequence needed.
It works for when you want row numbers too ie rather than enter row 1 as a hard coded "1" then have the following rows as a formula being previous row + 1 ie A2 + 1. You can have a consistent formula being N(A1) +1 copied down as N(A2) + 1 etc.

 

[RapchikM]

Dear Alex Sir,

Thank you so much for the detailed explanation. This is now understood with much clarity and will remember for future.

Thanks once more for the reply and solution.

Regards
RapchikM