Range of Units_____(P) Tier_____(G) Tier____(S) Tier
>=10_____<=24_____$3.50 _____$3.65 _____$3.75
>=25_____<=49_____$3.33 _____$3.47 _____$3.56
>=50_____<=99_____$3.19 _____$3.32 _____$3.41
>=100____<=200____$2.91 _____$3.03_____$3.11
>=200_____________$2.38 _____$2.48 _____$2.55
________TIER_____QTY____UNIT $_____Total Price
Example: P _______62_______?____________?
(the data above is how i have it in excel)note the >=< signs are not in excel i put them up there to show you the range!
So in the example above if a user typed if Tier = P and QTY= 62 the formula would solve the next two cells to be Unit $ = 3.19 and Total = 197.78.
(since 62 is >50 and <99 and its true that they selected Teir P the formula would know that the price is $3.19 so we can conclude that $3.19x62 = $197.78)
so what I am looking for is to type in the Tier Type and Quantity as shown in the example above and have the formula spit out the unit price that corresponds to the number of units based on the data above, (so the quantity entered will know where it falls under, and then it will have to know what teir was entered, P,G or S. to select the correct unit price) Finally then multiply the units by the coresponding price, to give me the final total? Is this possible? I hope its clear and not to confusing? Thanks for any help!!!
>=10_____<=24_____$3.50 _____$3.65 _____$3.75
>=25_____<=49_____$3.33 _____$3.47 _____$3.56
>=50_____<=99_____$3.19 _____$3.32 _____$3.41
>=100____<=200____$2.91 _____$3.03_____$3.11
>=200_____________$2.38 _____$2.48 _____$2.55
________TIER_____QTY____UNIT $_____Total Price
Example: P _______62_______?____________?
(the data above is how i have it in excel)note the >=< signs are not in excel i put them up there to show you the range!
So in the example above if a user typed if Tier = P and QTY= 62 the formula would solve the next two cells to be Unit $ = 3.19 and Total = 197.78.
(since 62 is >50 and <99 and its true that they selected Teir P the formula would know that the price is $3.19 so we can conclude that $3.19x62 = $197.78)
so what I am looking for is to type in the Tier Type and Quantity as shown in the example above and have the formula spit out the unit price that corresponds to the number of units based on the data above, (so the quantity entered will know where it falls under, and then it will have to know what teir was entered, P,G or S. to select the correct unit price) Finally then multiply the units by the coresponding price, to give me the final total? Is this possible? I hope its clear and not to confusing? Thanks for any help!!!