Hello everyone,
I have price level from level 1 to level 5, but differentiate by lead days, as example: Group A1 for 0-30 days from today consist of price level 1, price level 2, price level 3, price level 4, price level 5, Group A2 for 31-60 days consist of price level 1, price level 2, price level 3, price level 4, price level 5, And Group A3 for more than 61 days from today consist of price level 1, price level 2, price level 3, price level 4, price level 5. I have to set based on lead days and price level is based on demand, like 0-30% will be based on level 1, 31-40% based on level 2, 41-50% based on level 3, etc to level 5.
So far I am trying to use below formula but does not work, sorry I am just new to excel and I tried to several formulas but still not working..
DATA is the sheet where I put the price level data E is the price. column A is date, C is level based on the demand.
=IF($A1>TODAY()+60,(IF($C1="LEVEL1",'61DATAE$10,IF($C1="LEVEL2",'61DATAE$9,IF($C1="LEVEL3",'61DATAE$8,IF($C1="LEVEL4",'61DATAE$7,'61DATAE$6))))),IF(AND($A1>TODAY()+30,$A1<=TODAY()+60),IF($C1="LEVEL2",'31DATAE$9,IF($C1="LEVEL3",'31DATAE$8,IF($C1="LEVEL4",'31DATAE$7,'31DATAE$6)))))*(IF($C1="LEVEL2",'0DATAE$9,IF($C1="LEVEL3",'0DATAE$8,IF($C1="LEVEL4",'0DATAE$7,'0DATAE$6))))
Thank you in advance for any help from you.
Bookmarks