Werzit

Research
Excel

"In God we trust.
All others we monitor."
Economics | Radio| Regions| Television | Weapons | 
    
Excel If Statement

CountIF Counting markets per parent
  • =COUNTIF(C1:C17,"=Gannett")
  • For instance, how many markets at Gannett, McClatchy, and Tribune are on the list?

CountIF Counting markets above meeting certain number of sales
  • =COUNTIF(D1:D20,">50")
  • For instance, how many markets sold at least 50 listings?
CountIF
  • =IF('Sandbox Raw Data'!N7>0,'Sandbox Raw Data'!C7,"")
  • This formula will display markets assigned to a specific sales rep, for instance, Katie

Difference in Real Numbers, When Given a Percentage
  • =((100-C2)/100)*A2
  • This formula is used when we know a value for September, and we know the percentage change from a previous month (such as September of the previous year)
Forecast increase/decrease by a fixed percentage
  • A1+(A1*.04)
  • or
  • A1+(A1*B12)
  • another way is to assign the fixed percentage to a given cell, such as B12, in this example

Given Pct, what is the raw number
  • =(100/E3)*B3
  • This formula is used when we know the current numbers, and we are given the percentage change from a year ago, but do not have the actual numbers from a year ago. This formula lets us calculate where we were one year ago.

Nested IF statements, with text included
  • =IF((AND(B2="West",C2>10000)),"Senior","Junior")
  • another way is to assign the fixed percentage to a given cell, such as B12, in this example

Percentage Change Percentage Change, from Last Month to This Month
  • (B7/B8)/-1
  • If March is B7
    AND
    February is B8
  • This formula reports the percentage change, from one month to another

Raw Change Raw Change, from Last Month to This Month
  • B7/B8
    If March is B7
    AND
    February is B8
  • This formula reports the absolute numerical change, from one month to another

Sum Summing ad types per parent
  • =SUM((A1:A7="Gannett")*(B1:B7="Text Ad"))
  • This formula counts the number of specified types of ads by the specified parent.
  • For instance, show the parent company Gannett's sales of Text Ads.
  • This can be paired with a similar formula next to it, for counting the McClatchy text ads.
  • Similarly, other ad types can be counted, for instance, graphic ads, etc.
  • For summing up types of ads per parent company
  • For instance, how many text ads versus graphic ads did Gannett, McClatchy, and Tribune sell each?
  • Note, this formula is MORE specific than simply counting the total number of ads.

SumIF Summing ads per parent
  • =SUMIF(B1:B300,"=Gannett",C1:C300)
  • This formula counts numbers in column C, such as number of Ads, where column B is parent company name of "Gannett"
  • For summing up total ads per parent company
  • For instance, how many ads did Gannett, McClatchy, and Tribune sell each?
  • Note, this formula is LESS specific since it simply counts the total number of ads.

Velocity Calculations  
  • =B3/C3
  • In this case, we will see a value of 1.217 as the calculated velocity
  • A calculated velocity of 1.000 indicates zero change.
  • A calculated velocity below 1.000 indicates a slowing down, a decrease in the growth
  • This calculation converts the rate to a level that can be easily compared, from one customer to another.