Forum Discussion
Excel: Perform the calculations, in order to get the total amount spent for the year
I have been given 3 tasks and I complete the first one with VLOOKUP but have no idea how to do the other 2 tasks and I have less than 3 hours to complete, can anyone help me, please? Thanks a lot in advance!
Task 1 - Please Match the correct location code from table 1 with the candidates' current location in Table 2.
Task 2 - Please perform the calculations, in order to get the total amount spent for the year, on printing (after the monthly discounts).
Task 3 - You have a report of the last 3 quarter expenses for printing. Please calculate how much the employees from Havana have spent on printing and designate which of them has spent the most.
I don't know how to upload the excel table in here so I'll give a link to google docs: https://docs.google.com/spreadsheets/d/1oyqnOX2kqd5fTSnWrPoQPtS2IqrompSZ/edit?usp=sharing&ouid=108886739779250031460&rtpof=true&sd=true
4 Replies
Task 2:
=SUM(C4:C15*(1-D4:D15))Task 3a:
=SUMIF(Table3[Location],"Havana",Table3[Order for])3b (An Insider function is being used):
=LET(f,FILTER(Table3,Table3[Location]="Havana"),TAKE(SORT(f,3,-1),1,1))- This sounds like an assignment for school, quite possible for a class in the use of spreadsheets. Is that the case? Assuming it is, I think you should be talking with fellow classmates or the instructor rather than coming here for somebody else to resolve those tasks for you.
Come to think of it, you're beyond the three hours deadline anyway.......mathetes Not sure what I can understand from your reply. I'm not in school and I'm not any good with Excel. I'm 26 and I'm working.
Also yes, it's past the deadline but I still want to know the answers so that I can learn.
I replied that way because there are many precedents of students (college, elsewhere) coming here to get their homework done. The question you posed and the spreadsheet accompanying it bore all the indicators of falling into that same category. Note, for example, the header of the spreadsheet you linked to:
"MDM Test 2": That's why I responded as I did. This has all the characteristics of a homework assignment.
That said, the best way to learn Excel is to keep plugging away, looking at resources that explain the various functions. So I'll point you in the right directions, but leave a good deal of the work to you.
For Task #2, I'd like to assume you can figure out the basic math to determine how to take 8% from $400 to yield the amount actually spent. The way to write that in an Excel formula is almost the same as writing it in an arithmetic formula. Multiplication is accomplished by the * character. So =$400 * .08 would give you the discount. Looked at another way, =$400 * .92 would give the discounted amount itself. So you do that with all those amounts and percentages and then add up the results.
For task #3, things get a lot tricker. And part of the solution could depend on what version of Excel you have. I was able to solve it using the FILTER and UNIQUE functions to get the names of the individuals associated with the location "Havana".
Then the SUMIF function to identify how much each person had spent.
Here is a link to learn about the FILTER and UNIQUE functions, but be forewarned you'll need a current version of Excel to take advantage of those very useful functions.
Here is a link to learn how SUMIF works.
