Calculate Amount based on Lane ID and Weight Break

[mk_santhosh]

Hi Friends,

Need to a formula to calcuate Amount based on weight break.
Please find attached excel file.

There are 2 tabs. Rate sheet and Data

I need a formula to calculate amount based on weight break (<5,>5,>15,>25,>50,>75)
The lane ID should be match to calculate the amount.

Put formula in yellow cell.

Its urgent . Please help me on this.


Regards,
Santhosh

[FDibbins]

Hi and welcome to the forum

You start your table off with >5 followed by >5...meaning only 5?

If you change your headings from text <5, <15 etc to actual numbers...0, 4, 14 etc, then you can use this, copied down...
=INDEX('Rate sheet'!$A$2:$G$8,MATCH($A2,'Rate sheet'!$A$2:$A$8,0),MATCH(B2,'Rate sheet'!$A$2:$G$2,1)+1)

[RobertMika]

Copy of Rate Calculation-1.xlsx
Try of this work for you

[FDibbins]

Rob, that's pulling from 1 column to the left (I know, I did the same thing) 27 and 1 should be 0.3, not 0.2...<50 but not <25

[RobertMika]

Rob, that's pulling from 1 column to the left (I know, I did the same thing) 27 and 1 should be 0.3, not 0.2...<50 but not <25

Hmm, Depends if you look at spreadsheet(>5,<5,,, or request <5,>5).
But maybe I'm wrong...

[FDibbins]

yes lol, between the 1st and 2nd rules, nothing will show...>5 and <5 (my comment in my 1st post was incorrect)

[mk_santhosh]

Thanks to everybody who are supported to my query. Formula is working fine.

[RobertMika]

You are welcome.
Please leave reputation if you satisfied with the results.