Calculating spend averages with excel for online ads
A while back while talking to a friend about his career changes he uttered the following phrase: “Yes it’s different but it’s also the same, everything leads to Excel”, me a self declared fan of Excel and anything that might make my work life easier smiled and laughed. This phrase is so real and there are still many who can’t seem to accept it, people still struggling with this basic but so powerful program.
Having already accepted this as a general truth, I have been slowly trying to dominate this program as much as possible, so today I share two simple concepts that completely changed Excel for me and made me be a lot more creative when using this program.
The conceptS I want to share are:
- Every cell is a representation of the formula bar, what this means is that what you are seeing in your cell does not necessarily mean that the cell holds exactly that but a formula that has that as a result. This concept is essential when troubleshooting and doing quality assurance. If there is an error look at the formula bar.
- Excel is not a numbers/math program but a logic and programming program with an excellent User interface. This change in mentality will allow you to test the limits of this program and try to automate and resolve issues in a formula/function way instead of a manual way.
One particular example that holds these two concepts is a running 7 average on spend. This type of math is done constantly to decide if you are overspending or under spending and forecast spend for the future. The number of days might change for specific decisions, maybe it’s a 3, 5 or 10 day average.
The options are to manually add the spend for the desired amount of days and divide it. All good up until now, but when we are doing this multiple times a day and we have to repeat this every day this simple task can become cumbersome. We can be more creative and use Excel to directly solve this for us.
7 day average spend that restarts every month, column A holds the dates and column B holds the daily spend.
=IF(TODAY()-7>(TODAY()-(DAY(TODAY()))+1),(SUMIF(A2:A32,”>=”&(TODAY()-7),B2:B32)/7),(SUM(B2:B32))/(DAY(TODAY())-1))
Here we are using some basic everyday functions (if, sumif, sum, today, day and some other basic math like +, – and / )
This is the translation into English of every section.
This cell will hold the result of the following operation
=
if the date is less than seven days before yesterday do the following step if not jump to the next one
IF(TODAY()-7>(TODAY()-(DAY(TODAY()))+1)
Sum the spend of the days that are less or 7 days ago and divide that into 7
,(SUMIF(A2:A32,”>=”&(TODAY()-7),B2:B32)/7)
Find the monthly average by adding up all the spend held in column B rows 2 to 32 and divide them by the number of days in today’s date minus 1, so if today it’s the 29 we will divide them in 28.
,(SUM(B2:B32))/(DAY(TODAY())-1))
If your data doesn’t start over every month you can keep this formula and just keep the sumif section.
The important thing here is to be creative and explore Excel, a bit of logic and creativity will make you dominate this tool but the best will be that you will get time back.
Comments are closed here.