How to apply a discount in excel 2010

Good Morning!

I'm looking for a way to use the =hlookup


This is question:

In Cell I14, calculate the shipping discount using Shipping Discount 2 based on the total price (column F) The discount will be the corresponding percentage times the total shipping cost previously calculated. Write the formula so that it can be copied into cells I15:I19.


I wrote this

=-HLOOKUP(F14,$I$7:$K$9,3,TRUE)*H14

and I end up with

"-" in the cell. I thought maybe if I copied it to each cell it would change and nope!

Thank you!

Originally Posted by JBeaucaire

Your formula construct is exactly right in layout. But your reference to the Discount Table is off by one row. The table range is $I$6:$K$8 ... an easy mistake. Fix that in your I14 formula, then copy down.

You're still going to get "-" in the cells that result in zero, like I14. The search value of $171 falls into the first discount column range, which is zero discount.

JBeaucair:

Thank you again for your assistance. I have added to your reputation.

Enjoy your day!