Forum Discussion
Help! I know so little about excel that I can't even explain what I need!
Hello:
As chair of a 5 member Search Committee, I'm trying to streamline the search process and also trying to ensure, at least as much as possible, that every candidate is provided with the same opportunity to succeed. The one committee will undergo two searches for two different positions. The first step in the process is for the members to review the candidates' application packets independently. Next, the committee meets to discuss the independent reviews to ensure that inadvertently valuable information was not missed, and the final step is to rank the applications.
For this, we created a rubric with minimum and preferred qualifications, in total, there are 12 criteria; 3 for minimum qualifications and 9 for preferred. Some are straight forward, e.g., yes/no, number of publications, but others such as funding record, need to be divided up into federal grants, foundation, and state grants, where in federal grants are the most desirable, then foundation grants, and lastly state grants. Thus we assigned a greater value to federal (3 points) than to foundation (2) and state (1) grants.
I (who have no Excel experience other than to make little lists and add them, but who, regrettably, has an over inflated belief that, "Yeah, I think I can do that! and also love technology until time constraints for me to throw in the towel, like now.) created a workbook with two rubrics, one for each position. Additionally included are a master spreadsheet with the rubric for each position where I plan to list each candidate along with their scores. Finally, the workbook will contain a spreadsheet for each of the positions with associated the rubric.
I'm hoping that what I plan to do is possible in Excel. For the funding criteria that I mentioned above that need to be multiplied by 3 or 2, I added a column for the number of federal grants, another column for the constant, 3, and an additional column where I inserted this formula: =the cell number for federal grants*3 then I hit enter. And, low and behold it worked. I did the same for foundation grants. I then dragged the constant column and the formula column down for both the federal and foundation grants, and that worked too. That's where my success ends. There are two other things that I need to learn how to do in order for me to distribute the workbook to the committee members, which was due 3 days ago. Yikes.
This is what I need at this point:
- to create a formula that will add up the rows and give me a total score for each applicant
- an additional formula that would paste each row of scores including the total score in their corresponding master spreadsheet ranked from highest to lowest total score
This is what I've tried unsuccessfully to do to accomplish the first issue, #1. I've tried multiple time to create this formula: =SUM(K7:BC7)-M7-N7 etc. I had hoped that including the -M7-N7 in the formula would exclude the columns with the #of federal and foundation grants and the columns with the constants. However, cell with the formula returns an error: #VALUE? and I can't for the life of me fix it. And, as for issue #2, I haven't even tried to do that one. Would someone give me some direction. At this point, I'm willing to pay someone to do this, can you all recommend someone, please. Thank you so much for your time and consideration of my issues.
- if the linked sheet I did 2 things:
a) the TOTAL formula for each candidate I changed to:
=SUM(K7:AJ7,AR7:BC7)
because columns AK - AQ are all summed up in AR so i skip them so you don't 'double add' them
b) I reformatted the sheets so COLUMN A is the list count (1,2,3,4,...) and column 2 is the date to make the sorting/formula easier assuming you want the count 1,2,3,4 to still be that way on the master. If on the other hand you want the list 1,2,3,... from the CANDIDATE sheet to stay with the candidate so the MASTER sheet is NOT in order but rather refers to the corresponding number on the other sheet, we can shift the SORT formula to column A
c) I added a basic SORT formula onto the MASTER and reference the entire table from the CANDIDATE sheet and sort by the last (TOTAL) column highest to lowest.
d) I added an IF() statement just to blank out the 0 values to make it look cleaner.
8 Replies
mtarler's recommendations may be sufficient for you, but I am not certain of the structure of your data in the workbook. See all sheets in the attached workbook for considerations and steps.
- Hi SnowMan55. I'm so grateful for your help. Since I know so little about the proper terms to use, I've included a OneDrive link to a dummy workbook with two spreadsheets, one is a master where I'd like to move the candidate's row from the individual spreadsheet to the master so that we can have all the candidate's values and total score on the same spreadsheet to enable us to rank them.
https://bama365-my.sharepoint.com/:x:/g/personal/talamedalawson_ua_edu/EcuSakaN_9RErWGYOsOBQKkB1iY9AAWR2fgyfv3_GPt60Q?e=ZisQLx
Again, thank you so much for your willingness to help a total stranger! There's a lot going wrong in the world today but, undeniably, there's also a lot of good. Hope you're having a good Sunday.- I neglected to clarify that the individual spreadsheets are for the independent reviews. After we discuss our independent reviews then I'd like to move the rows to the master spreadsheet.
- if the values in col M and N are non-numbers then =SUM(K7:BC7) should ignore them just fine. as for #2, on the master you just want to type = and then go to the corresponding sheet and highlight the range of data you want "copied" there and you should get something like: =Candidate1!K7:BD7 You can make is a little easier if you list the TAB names on the master (let's say in A1:A5) then next to it (i.e. in B1) you can type =INDIRECT(A1&"!K7:BC7") and then you can fill/copy down.
- Hi,
try this for point #1
=SUM(K7:L7,O7:BC7)- Thank you, ITTom365. I'll give it a try.
