If then question in excel
I am trying to calculate points awarded in an excel file where if a person doesn't have a total weight in column h3 they would get 30 points otherwise it would go off there ranking in column i3 where is would reference a chart for the points.. I am not able to get this to work...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What does the chart look like - do you mean a sort of lookup table? Maybe use a formula like
=IF(H3="",30,LOOKUP(I3,tab
where table is a two column table with the lower bounds of each weight range in ascending order in the first column and the value to be assigned for those ranges in the second.
regards, barry
=IF(H3="",30,LOOKUP(I3,tab
where table is a two column table with the lower bounds of each weight range in ascending order in the first column and the value to be assigned for those ranges in the second.
regards, barry
ASKER
Ok I am attaching the file, if you goto week1 tab I am trying to get the points awarded to calculate, so that if the total weight is 0 then they will only get 30 pts, but if there is a weight then it will award point via the rank in column I that references the points tab.
Book2.xls
Book2.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now is there an easy way to get this info to summarize from week 1, 2, 3, 4, 5, classic to pull in the names from each of those weeks to sheet 11 except duplicates so I can tally a total from week to week?
You can use
=IF(H3=0,30,--VLOOKUP(I3,P
and copy down.
To do this for all weeks you can select all those sheets by pressing Shift and then do it on any one sheet it will be replicated on all sheets.
Saqib
=IF(H3=0,30,--VLOOKUP(I3,P
and copy down.
To do this for all weeks you can select all those sheets by pressing Shift and then do it on any one sheet it will be replicated on all sheets.
Saqib
Sorry misread your comment 35780047
Are you interested in a macro for this?
ASKER
So another question I have a Names tab that I want to be able to automatically pull them into a summary tab, but want it to auto update when a new name is added. I made the names and ID a table will this help?
ASKER
How do I solve this if then statement. I need a cell to sum accross tabs if certain cell match each other. For instance in the summary page I have a cell that contains 1107-01 and then in tab a, b, c, d if they have that cell I want them to add a column in that field with a, b, c, d
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
=IF(H3="","30 points",I3)
Essentially, if H3 is blank (you could amend the parameter here accordingly), then "30 points" will be displayed but if it is not blank (again, you can change the parameter) then display the contents of cell I3.