Last Updated on
Ever since I became a SEO consultant, I am forced to have Excel as my best friend. I had been using Excel since I was in college but never realized how many different functions Excel had.
So, in order for us to run a successful online business, Excel is a must. This is why once in awhile I am going to post useful Excel functions as I come across.
While developing a revenue prediction model for Amazon affiliate income, I came across to a need to use an excel formula to output a different number depending on the input.
Basically, Amazon pays its affiliates different percentages depending on how many goods they sell within that month.
The challenge is to make excel utilize different commision according to the predicted number of goods sold.
In other words, if the prediction model says 100 items will be sold that month, the equation needs to pull “6.5%” commission for calculation (see table below).
After researching the web, there are various ways to do this and I ended up settling with IF (a,b,c) & IF (d,e,f). It basically adds a string of “IF” equations by using “&” sign.
In addition, I also needed to specify the range of the number of items sold for each commision level. This is where the “AND” function comes in.
So by combining the two, the formula looks like this.
If we translate the formula into English it’s something like, “If the number of item sold is between 1 and 6, display 4%, if not blank” & “If the number of item sold is between 11 and 30, display 6%, if not blank”.
The screenshot below shows what it looks like in Excel.