#38

get a computer i reckon

#39

Presumably if you want to look at year x month y measure z, there will only be one row that meets all of those criteria? If so…

Replace x with your last column of products
Countif(Filter(d:x,a:a=“2020/21”,b:b=“july”,c:c=“spend”),“r”)

1 Like
#40

You are very good and helpful. Thank you!!

1 Like
#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