Trying to create a mini rating system

[LtCmdrData]

Hi,

I hope someone can help or at least point me in the right direction. I am trying to create a what I'll call a mini rater. I want it to work like this: The user is prompted to enter a state from a drop down list. Next the user enters a weight that they want calculated. I have two columns of data behind the scenes. I need to find the row where the users weight falls between the values in the two columns. Next it looks to the right and finds the rate associated with the two columns or bracket. Finally it multiplies the weight entered by the user times the rate and returns a value. Below is an example of the data. Should I even be trying this in Excel? Or is this better suited for Access?

[TABLE="width: 512"]


<colgroup><col width="64" style="width: 48pt;" span="8">
<tbody>[TR]

[TD="class: xl63, width: 64, bgcolor: transparent"]Del St[/TD]

[TD="class: xl63, width: 64, bgcolor: transparent"]Act Lbs[/TD]

[TD="class: xl63, width: 64, bgcolor: transparent"]Rate[/TD]

[TD="width: 64, bgcolor: transparent"][/TD]

[TD="class: xl64, width: 64, bgcolor: #4472C4"]delstate[/TD]

[TD="class: xl65, width: 64, bgcolor: #4472C4"]minW[/TD]

[TD="class: xl65, width: 64, bgcolor: #4472C4"]maxW[/TD]

[TD="class: xl65, width: 64, bgcolor: #4472C4"]rate[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]AL[/TD]

[TD="bgcolor: transparent, align: right"]2400[/TD]

[TD="class: xl72, bgcolor: transparent, align: right"]47665[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]AL[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]45.82[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]AL[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]1000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]1999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]33.00[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl69, bgcolor: yellow"]AL[/TD]

[TD="class: xl70, bgcolor: yellow, align: right"]2000[/TD]

[TD="class: xl70, bgcolor: yellow, align: right"]2999[/TD]

[TD="class: xl71, bgcolor: yellow, align: right"]19.86[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]AL[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]3000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]3999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]16.09[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]AL[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]4000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]4999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]10.68[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]AR[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]43.48[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]AZ[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]2000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]2999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]28.99[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]CA[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]104.17[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]CA[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]1000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]1999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]62.98[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]CO[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]92.67[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]CT[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]6000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]6999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]13.80[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]CT[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]9000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]9999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]12.78[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]CT[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]10000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]10999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]12.44[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]FL[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]87.07[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]FL[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]1000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]1999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]29.75[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]FL[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]2000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]2999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]28.77[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]GA[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]31.32[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="class: xl66, bgcolor: transparent"]GA[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]2000[/TD]

[TD="class: xl67, bgcolor: transparent, align: right"]2999[/TD]

[TD="class: xl68, bgcolor: transparent, align: right"]22.35[/TD]

[/TR]


</tbody>[/TABLE]

 

[njimack]

Confirm with ctrl+shift+enter:

=INDEX(Table1[rate],MIN(IF(Table1[delstate]=A2,IF(Table1[maxW]>=B2,ROW(Table1[delstate])-1))))

 

[cunningAce]

Give this formula a try

=SUMPRODUCT((A2=E2:E19)*($B$2>=F2:F19)*($B$2<=G2:G19)*(H2:H19))*$B$2

A2 is user state input cell
B2 is user weight input cell

 

[LtCmdrData]

Thank you Neil. That accomplished what I was trying to do. If you don't mind could you explain in plain English how the formula works? I'm still new to these nested formulas and such.

 

[njimack]

Thank you Neil. That accomplished what I was trying to do. If you don't mind could you explain in plain English how the formula works? I'm still new to these nested formulas and such.

The MIN function looks at all rows where [delstate] is equal to A2 and [maxW] is greater than or equal to B2. It then takes the row number (minus 1 to allow for the headers in row 1) and uses this in the INDEX formula to return the correct record from the [rate] field.

Hope that helps!