Hi, I need to create two excel formulas to show the number of orders in each month, but they are dependant on how many orders have been cumulatively placed in the year to date.
Basically if the cumulative number of orders are less than or equal to 100, then I need to know how many orders were placed in the month.
Secondly if the cumulative number of orders is more than 100, I need to know how many of those orders were placed in the month.
The reason for this is that for up to 100 orders we have one price, but for >101 we have another price.
E.G. If we had 50 orders in Jan, 30 orders in Feb and 40 orders in Mar and 60 in Apr, that is a total of 180 orders. Cumulatively that's in 50 Jan, 80 in Feb, 120 in Mar and 180 in Apr. The In Jan they would all be at the lower price, same for Feb, but in Mar we'd have 20 at the lower price and 20 at the higher price, then in Apr it would be 0 at the lower price and 60 at the higher price.
I'm tearing my hair out trying to come up with a formula which is consistent every month without having to manually tweak it.
Can anyone help me? I'd be so grateful if anyone can help.
Bookmarks