Can someone help me to correct the formula and arrive at correct result ?

[RapchikM]

Hello

I think i've made a mistake in writing the formula to derive the exact answer

E	F	G	H	I
000-100 Units		110	2.18	239.8
101-300 Units		220	5.36	1179.2
301-500 Units		220	11.62	2556.4
Above 500 Units		171	12.56	2147.76

E29 = 721

Formula in H21 =IF($E29<=$G$18,$G$18*$H$18,IF($E29<=$G$19,(($G$18*$H$18)+($E29-100)*5.36),IF($E29<=$G$21,(($G$18*$H$18)+($G$19*$H$19)+(($E29-300)*$H$20)),(($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E29-$G$21)*$H$21)))))

answer = 10883.40 instead of 6123.16

another try
E31 = 721
Formula in H21 =IF($E31<=$G$18,$G$18*$H$18,IF($E31<=$G$19,(($G$18*$H$18)+($E31-220)*5.36),IF($E31<=$G$21,(($G$18*$H$18)+($G$19*$H$19)+(($E31-220)*$H$20)),(($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E31-(G$21+G$20+G$19+G$18))*$H$21)))))

Answer is 3975.4 instead of 6123.16

So summing of 239.8+1179.2+2556.4+2147.76 = 6123.16

Your help shall be appreciated

Thanks
RapchikM

 

[Dave_george]

Try this

=IF($E29<=$G$18,$G$18*$H$18,
    IF($E29<=$G$19,($G$18*$H$18)+(($E29-$G$18)*$H$19),
    IF($E29<=$G$21,($G$18*$H$18)+($G$19*$H$19)+(($E29-$G$19)*$H$20),
    ($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E29-$G$21)*$H$21))))

 

[Peter_SSs]

1. We cannot tell what row numbers you have shown. Please consider XL2BB for sample data and expected results to make it easier for your potential helpers.
   
   
2. You have listed two attempted formulas which you say are in H21. However, both formulas use H21 which causes a circular reference.
   
   
3. It may also help if you explain in words exactly what you want the formula to do.

 

[kvsrinivasamurthy]

Instead of formula pl explain in detail what is required.

 

[RapchikM]

Dave_george

=IF($E29<=$G$18,$G$18*$H$18, IF($E29<=$G$19,($G$18*$H$18)+(($E29-$G$18)*$H$19),IF($E29<=$G$21,($G$18*$H$18)+($G$19*$H$19)+(($E29-$G$19)*$H$20),
($G$18*$H$18)+($G$19*$H$19)+($G$20*$H$20)+(($E29-$G$21)*$H$21))))

Your answer is also displaying 10883.40

Peter_SSs Sir

1. We cannot tell what row numbers you have shown. Please consider XL2BB for sample data and expected results to make it easier for your potential helpers.

I tried incorporating XL2bb somehow it did not work unfortunately had to adopt tradtional mehtod of incorporating Table
Edited below
when Opeing Xl2bb
Also i got error
This File type is not supported in Protected View

2. You have listed two attempted formulas which you say are in H21. However, both formulas use H21 which causes a circular reference.

Again I made the blunder while posting the thread hurriedly. the second should have been H31 instead of H21.

3.It may also help if you explain in words exactly what you want the formula to do.

kvsrinavasmurthy
Instead of formula pl explain in detail what is required.

It is the number of slab of units consumed with the range given in E Column and units to be taken into account from Column G.
And Column H is the rate.
Column I it is Amount ie Col G * H

My Total Units have been consumed as 721 in E29 so with the formula I am trying to derive figure as 6213.16

is as follows for
so therfore 1st Slab 110 units consumed 110 X 2.18 = 218.0
2nd Slab 220 units consumed 220 X 5.36 = 1179.2
3Rd Slab 220 units consumed 220 X 11.62 = 2324.0
4th Slab above 500 so therfore (721-220-220-110) = 171 X 12.56 = 2147.76
So therfore summing from 218+1179.2+2324+2147.76 = 6123.16

Kindly bear with the above Strucuture

Thanks
RapchikM

 

[Peter_SSs]

I tried incorporating XL2bb somehow it did not work unfortunately had to adopt tradtional mehtod of incorporating Table
Edited below
when Opeing Xl2bb
Also i got error
This File type is not supported in Protected View

Review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page I linked above.

 

[RapchikM]

Peter_SSs Sir
Review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page I linked above.

I uninstalled the last time downloaded deleted too and emptied from Recyclebin too and again downloaded. As per the instructions on the page. Implemented the correct thing.
I opened New Xls file for addins.Yet I get same Error This File type is not supported in Protected View

 

[Peter_SSs]

Sounds like you may not have read my message clearly enough

Review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page I linked above.

If you read the linked thread in detail you will find that your issue is most likely that you have not put the file in a Trusted Location.

 

[RapchikM]

Just Can't Implement XL2BB As per your image with two links you sent Went thoroughly but can't get XL2bb.xlam
It is showing the same message This File type is not supported in Protected View.

If there are amy other methods for me to implement. you could please instruct me
I am getting Trusted Location : C:\Users\RM\AppData\Roaming\Microsoft\Excel\XLSTART\

Although file XL2bb.xlam copied in C:\Users\RM\AppData\Roaming\Microsoft\Add-ins\

What to do ?

RapchikM

 

[Peter_SSs]

Although file XL2bb.xlam copied in C:\Users\RM\AppData\Roaming\Microsoft\Add-ins\

If that path is not shown in your Trusted Locations (File -> Options -> Trust Center -> Trust Center Settings ... -> Trusted Locations) then use the 'Add new location ..' button to make it a Trusted location or else move the xl2bb.xlam file to a folder (path) that is already a Trusted location & go through the installation steps again

I am getting Trusted Location : C:\Users\RM\AppData\Roaming\Microsoft\Excel\XLSTART\

Also, can you confirm that you have performed this action as set out in that 'XL2BB Icons greyed out' thread?