Sorry, long & tedious but HELP, Pleasy !
(I am the only available “expert” at work and this is well beyond me)
Humour me – I’m not quite this sad honest, but let’s imagine I wanted to produce a load of graphs charting members’ posted bikepics.
I’d score them according to 6 criteria like colour, lawn condition etc (one point for each acceptable answer) and then award a % score (ie total scored divided by possible score).
In my image, a single row would be populated for each bike pic with the answer (chosen from a drop-down list of options as shown) displayed as text for use in later charts.
Then some sort of countif type thingy would assign a score for each criterion – the “OK” ones score 1 point and the “No” ones score zero.
However some answers can’t be scored (“N/A”) and so the possible max score for a pic isn’t always 6 (non-applicable criteria must be removed to allow say 5 out of 5 rather than 5 out of 6)
in my sheet, column H would have the actual score (easyish), column I would have max available and I don’t know a good way to do that automatically (can “IF” do that sort of thing too ?).
Then J has the % result for each pic (H/I)
I also want to make a chart of average (mean) % score for all pics posted each month, accumulating from now on. (so, Nov | Dec | Jan | etc ) and I don’t really know how to do that either.
I reckon I could use a pivot table, group the dates and have the “average” of all scores displayed by month – is that any good ?
stoner, sorry, should’ve said that numbers in brackets are just to show you lot what score might be assigned, they’re only text entries in the actual output. Also, the output format is already set and comes pretty much as I showed there. There are no pre-existing columns for scores next to each entry and it’ll take literally months to get a software geezer to change the reporting format so I’m hoping to just tack on extras at the end (though actually individual scores just using “If” would be ded handy if there was an automatic way to make it create them – macros I suppose ?)
How would you do the variable denominator bit? that’s what’s really causing the aggro – If told it to SUM values from 0,1 and N/A (or “Blank”) what would it do ?
stoner, it currently doesn’t occur on the sheet. hence me wondering about “IF” (might have to populate 6 new columns with the numerical score before I do the maths bits ?)
software geezer has some inaccessible dark art means to generate scores that get used in other ways by same system but he’s not really accessible to me to discuss this stuff and to arrange a change in output would take ages going by past experience (no money for proper work to be done as the available budget gets spent by the “bigger kids”)
ah, right.
Going by the initial scale of your example sheet, if statements could do it, but theyd be bloody clumsy.
You really need an indexed table through which the text outputs can be converted to a numerical result. The same table would also handle your denominator conversion as well. as above, VLOOKUP is your friend here, and SUM.
have used something like this before in similar but simpler circs:
=SUM(IF((T$37:T$65=”Correct”),1,0)/29)
blind alley then, I assume ?
I also want to make a chart of average (mean) % score for all pics posted each month, accumulating from now on. (so, Nov | Dec | Jan | etc ) and I don’t really know how to do that either.
I reckon I could use a pivot table, group the dates and have the “average” of all scores displayed by month – is that any good ?
you can create variable chart data fields using named ranges that change shape and location using the offset function in the range definition. Very powerful, but a bit black belt.
The second sheet give you all the answers, along with their associated score, and whther they contribute to the max score
Each of the drop lists is linked to a named range which is part of that second sheet
Vlookup looks up the given answer in the list on the second sheet, then returns the corresponding score/max score
Posted 12 years ago
Viewing 17 posts - 1 through 17 (of 17 total)
The topic ‘Tonight's lazygit Excel challenge (with pics !)’ is closed to new replies.