Look a fix qty into intervals of qty and show specific value

[carmelindazzz]

HI,

Can someone please help me with sthg? I look into queries on this forum , but didn't found it yet and I need.
I have some products that take different price for different ordered qty.
IF qty 1 => price is 200, if qty is (2-99) => 180, if qty is (100-299) the price is 170. How to match a fixed qty to the correct price? eg.if the qty =70..how to look it on the intervals? see file attached

thanks
Cami.

data.xlsx

[ConneXionLost]

Hi Camelia,

In your sample workbook, how do you match the PN to the price categories?

[thinkspac]

Hi Camelindazzz,

You need to put in the IF functions. Based on your worksheet, I have started for you. (I have used your thread example in your worksheet)

In cell K8, paste the following:

Please Login or Register  to view this content.

This means "if the value in cell J8 is equal to 1, the price is 200. If the value in J8 is less than 100, the price is 180. If the value in J8 is less than 299, the price is 170. If none of the following are true (over 299) the price is 170."


I hope this helps!

If you need anything, please message me.

[oeldere]

Maybe like this.

[carmelindazzz]

This is very good ,Thanks,but..
if the PN are in no particular order, like I put first time (1/2/3/1/1/2/2/etc) not 1/1/1/2/2/2/3/3/3..this kind of solution doesn't work. I have attached the updated file, where I tried to do this..
Another issue, but I think this could be solver easy, that in fact the PN cells are not like 1, 2,3,4...they are like a unique code, letters and numbers (UX200V,for example). I think I couldnt use the & if this is General data.
..
I'm lost..

Regards

[ConneXionLost]

Not a problem, you can do that with SUMPRODUCT like this:

Please Login or Register  to view this content.

but you stlll need to have a way of matching your PN numbers to the price categories. See column H in the attached.

[carmelindazzz]

Great ! this works perfect now.
I look for the Sumproduct definition , but I dont get what are these "--" , can you please explain me..because I want to understood it well, to know it next time.
About the matching I'm ok, I have the PN numbers, I made the ad notation 1,2,3, only for making it easier

Thanks a lot

[ConneXionLost]

The Double Unary Operator (--) converts a TRUE/FALSE to a 1/0 and allows Excel to use these in further math operations. Have a look here for a more detailed explanation.