Hi,
Apologies in advance if this isn't completely clear, I'm new to the forum so not completely sure on all the format rules for these posts!
I have a set of data that looks a little like the below:
Company Cost Type Number of Sales Rate Spend Sky CPS 10 £30 £300 Sky CPS 5 £20 £100 Skoda Fixed 15 £200 £200 Skoda Fixed 10 £200 £200 Skoda Fixed 12 £200 £200 Samsung CPS 10 £10 £100 Volkswagen Fixed 3 £500 £500 Volkswagen Fixed 4 £500 £500
I already have a nice formula that looks at what the cost type is and then calculates the spend correctly for CPS (Cost Per Sale) records. The difficulty I have is that I want to be able to accurately recognise the Fixed price records. In my table you can see for example we have 3 consecutive records for Skoda for a fixed price deal they have for £200. In reality this doesn't mean my spend for that company should be £600 though, it's a fixed deal so it should be £200 total spend for all their sales here, which means I want my Spend column to take into account how many consecutive rows have Skoda in column A and then divide the 'Rate' of £200 by that figure. This would mean Skoda's spend per row should actually be £66.66 (200/3). Is there a formula that could do this for me?
Please ask if there's anything I can do to clarify this.
Bookmarks