Question
Hello dear tutor, the screen shot attached in the below is my Excel assignment file. Can you help me with the Formula for each question?
Hello dear tutor, the screen shot attached in the below is my Excel assignment file. Can you help me with the Formula for each question? I couldn't put the file here because it shows an error. So, I wrote each question just write down the formula for each question. It's not necessary to write the formula with all the details, just show me how is it look like and I figure out the rest by myself. Thank you!
1)In cellI15, enter a function to extract the StartYearof Membership. Copy the function down to the other cells in the column.
InI1enter a function to calculate how many members started membership after the year2008.
2)In cellJ15,enter a function to extract the StartMonthof Membership. Copy the function down to the other cells in the column.
InJ1enter a function to calculate how many members started membership afterthe month ofMay.
3)In cellK15, enter a function to extract the StartDayof Membership. Copy the function down to the rest of the cells in the column.
InK1enter a function to calculate how many members started membershipon orafter the 15th dayof the month.
4)In cellL15, enter a function to calculate theDaysof Membership. Copy the function down to the rest of the cells in the column.
InL1enter a function to calculate theaverageDays of membership for all the members.Format as comma style, 0 decimal.
5)In cellM15, enter a formula to calculate theYearsof Membership. Copy the formula down to the other cells in the column.
InM1enter a function to calculate theaverageYears of membership for members in the Gardening Club.Format as comma style,1decimal.
6)In cellN15, enter a formula to calculate theDaily Cost of Membership.Copy the formula down to the other cells in the column.
InN1enter a function to calculate theaverageDaily Cost of membership forfemalemembers.Format as currency style, 2 decimals.
7)In cellO15, enter a formula to calculate theMonthly Cost of Membership.Copy the formula down to the other cells in the column.
InO1enter a function to calculate thetotalMonthly Cost of membership for memberships which started before the month of July.Format as currency style, 2 decimals.
8)In cellP15, enter a formula to calculate theMembership Cost for the period(from start to end of membership).(Hint:Multiply the Annual Cost of membership by the Years of Membership).Copy the formula down to the other cells in the column.
InP1enter a function to calculate thetotalMembershipCost for the period for all members.Format as currency style, 2 decimals.
9)In cellQ15, enter an IF function:
If theMembership Cost for the periodis more than $10,000,enter Yes,otherwise the cell should be bank.Copy the function down to the other cells in the column.
InQ1enter a function to calculate how manymembers got yes.
10)In cellR15, enter an IF function:
If theYears of Membershipis 5 or more, reduce the Annual membership by50%(Hint: multiply the Annual Cost of Membership by 50%),
If theYears of Membershipis 3 or more, reduce the Annual membership by25%(Hint: multiply the Annual Cost of Membership by 75%),
otherwise reduce the Annual Cost of Membership by10%(Hint: multiply the Annual Cost of Membership by 90%).Copy the function down to the other cells in the column.
InR1enter a function to calculate thetotalLoyalty Membership Renewal.Format as comma style,0decimal.
11)In cellS15, enter nested IF functions:
If theClubisScuba,enter500,
If the ClubisGardening,enter400,
If theClub isRock Climbing, enter700, otherwise enter300.Copy the function down to the other cells in the column.
InS1enter a function to calculate thetotalClub Discount.Format as comma style,0decimal.
12)In cellT1, enter the SUMIFS function to calculate thetotalAnnual Cost of Membership forfemalemembers of theWeight watchersclub.Format as comma style,0decimal.
13)In cellU1, enter the AVERAGEIFS function to calculate theaverageAnnualCost of Membership formalemembers in thegardeningclub.Format as comma style,0decimal.
14)Use theSimple Filter to answer the following question.
How many members arefemaleAND started membershipafter the Year 2005AND are in aWeight Watchers Club? Enter the answer inQ1.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started