#41

I am a massive nerd who actually likes helping people with spreadsheet problems. I would get a job doing it if i could

1 Like
#43

Oh look it’s me again.

After the startling success of my last set of questions, I now have a nice dynamic chart and table which will show how 10 teams are performing,

BUT

I now need to add something that’ll summarise the average performance of each team - just telling me which letter category the majority of cases fall into, so I guess returning the modal letter?

Eg in this table the first team have majority green

Polish_20201127_120416474
Polish_20201127_1204164741920×1407 400 KB

Is there a way to automate this to populate this crude little table down here?

Polish_20201127_120516526
Polish_20201127_1205165261141×1375 244 KB

Kind regards,

Scott_chegg

#44

You could make it more complicated than the mode. If one team had say 10 red, 1 amber, 12 green, would you want it to return green or amber? Because I would say amber for that but the mode will return green

I have done a similar red amber green thing and worked out the overall mark by assigning 1 point for red, 2 for amber and 3 for green, or something like that, then averaging. So like, for each item in col B, add up the equivalent in col C plus 2x col D plus 3x col E, divided by the sum of c:d for that row, then stick that inside an IFS to say if it’s <1.5 it’s red, >=1.5 and <2.5 it’s amber, >=2.5 it’s green

2 Likes
#45

That’s a very great idea. I’ll try this thank you!

1 Like
#46

Yes definitely recommend moving to a numerical system even if it’s based on the colours

1 Like
#47

This worked an absolute charm. Thank you so, so much.

1 Like
#48

Oh I dominate this thread.

What am I doing wrong here? Highlighted cell should ideally populate with “Dom1 Laptop”. Column J is another if statement & K is a vlookup, if that gives any more helpful context?! Sorry for the tiny picture also

image
image2460×402 90.5 KB

#49

Hello. I want to create a chart which shows changing league positions over X number of weeks. I have all the data per week. NOW WHAT.

Apple Numbers if that helps.

TIA in advance

#50

Bit hard to read the formula properly from the picture, can you copy and paste it here?

#51

At a preliminary guess i would say that it may not consider #N/A to be text, so if you replace both instances of K215="#N/A" with ISERROR(K215) it might work, maybe

1 Like
#52

Ah! Suspected that might be the case so I’ll try that. Thank you for helping me…again!

1 Like
#53

This, but I think you want ISNA() as that will handle only the NA case while ISERROR() will be true for any error case.

Or you could add something to the formula in col K to handle whatever is causing the #N/A. I guess that’s something not in a lookup table?

2 Likes
#54

Couldn’t get the ISNA working for some reason, but this fixed it. Thank you very very much!

2 Likes
#55

Help.

I have a set of student data, some students have multiple entries. I want to do some sort of lookup where if there are multiple entries for one student, the lookup will return the latest value. Data is sorted in chronological order if that helps.

#56

Something like this?

#57

Sort of yeah. So my data has a list of student names and who they were referred to for support. Some students will appear twice where they’ve been re-referred. I want the lookup to always return the last entry in the dataset.

#58

here is how I would do it. I would add a column called student name count, and in it I would have a count if formula, the first half would be anchored with the $ the second bit wouldn’t. So if the names are in column B the formula would be countif($A$1:A1,B1), when dragged down it would be ountif($A$1:A10,B10) in the tenth row etc. That means the number in column A would be the occurance of the name.

I would then have a table of the names, and use the max formula to see what the highest occurance of that name is, combine the name and max occurance into one ID, combine column A and B into one ID column and then do a vlookup or match to pick out the info. probably a neater way of doing it but thats how I would do it

#59

I think ttf’s solution works, but can also suggest this if you’ve got, or are able to add an ID column, or have a date column:

Screenshot 2021-02-11 at 16.12.17
Screenshot 2021-02-11 at 16.12.17960×516 39.2 KB

You should be able to replace the values in the “entry” column with dates and it’ll be fine so long as you don’t need to differentiate between two rows with the same date for a single student.

Brief explanation (as good as I can manage by text) of the formula: =INDEX(C:C,MATCH(MAXIFS(A:A,B:B,E2),A:A,0))

MAXIFS finds the largest value in column A, so long as the corresponding value in column B matches the value in E2, this is passed to

MATCH which looks for that value in column A, the ‘0’ indicates it must find an exact match, it then passes the location (index) of that match to

INDEX which returns the value in the corresponding location in column C

#60

Is it in excel (if so which version) or google sheets?

#61

(I am heavily reliant on FILTER() to solve all my problems and adding extra columns is, for me, an act of extreme desperation)