IF statements

Website: https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2


As a Project Analyst, I used IF statements to check how much credits were generated for a single employee in 2020. The if statements are used to check several things and can produce a result if the condition turns out true or false. When I joined the team, there was already a template that utilized embedded IF statements paired with other functions like vlookup because there were several tabs within one excel file. 

Below, I decided to play around with several IF statements and concluded with a SUMIF to calculate the credit generated for a single employee in 2020. 

The main idea with my formulas is to find at what quarter does the employee's gross pay goes over 10k. In this case for employee John, it was Q3. Therefore, I calculated 50% of his Q1 and Q2 gross pay to be credit generated. In Q3, he went over. Since I know 5,000 is the maximum generated credit for 2020, then I can deduct that from Q1 and Q2 generated credits to get the remaining. Finally, I used a sumif to only sum up the credits that I deemed "okay," meaning that those credits are generated within the 10k max qualified gross pay rule for 2020.

I have tested this with other numbers, so my formulas do work. I know there are ways to make a cleaner because unless you look at each formulas individually, it looks confusing at first glance!

Nevertheless, I am happy with what I produced below because typically I use someone's else template and here I created my own using if statements. Feel free to see below the logic behind each IF formula.

Comments

Post a Comment

Popular posts from this blog

Xlookup

Website: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) As a Finance & Accounting Specialist, I used the xlookup for each month's UC Davis Health billing. There are different ATB ("Authorization to Buy") numbers, estimate numbers, media types, and ATB names. Our goal is to to see how much we are billing for each ATB number to make sure we are in budget and to check if we are billing enough for each ATB, which we can be summarized using a pivot table of the full report data. Also, if "N/A" returns, then I know there is either a new ATB that I would request more info from the media team. However, as mentioned in its definit...
Read more

GETPIVOTDATA

Image
GETPIVOTDATA The GETPIVOTDATA function returns visible data from a PivotTable. Honestly, at its simplest, just use "=" and then click on a cell and then the whole syntax will autopopulate. That is how I returned the Billing Gross, Net, and Commission YTD. I think is helpful to use a data validation list. First, use "=" and reference a cell and then just change the item to reference the cell that has data validation. For example, there when you change the media type on the green cell F12, you can get its unbilled gross, net, and commission. Note that the data validation list dropdown may not appear unless you open the workbook on your Excel Desktop and open the workbook in Edit mode.  
Read more

Ranking of UC Schools from Top 110 Ranked National Universities of 2024

Link to GitHub:  https://github.com/sgng-hub/2024unviersityranking Link to Dataset: https://www.kaggle.com/datasets/raymondtoo/the-world-university-rankings-2016-2024 Dataset Official Source: https://www.timeshighereducation.com/world-university-rankings/2024/world-ranking This project is about writing SQL query to find the ranking of top 110 world universities that are part of the University of California System I included a link to Looker Studio visualization of the query     
Read more