Tonight's lazygit Excel challenge (with pics !)

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).

[/url]
bikepic scorer[/url] by scaredypants[/url], on Flickr

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 ?

How many years is it since you last had sex?

to make it simpler Id have a column next to each scoring column that just had a number (1, 0, N/A).

You can use
=LEFT(RIGHT(C1, LEN(C1)-FIND(“(“, C1)), LEN(RIGHT(C1, LEN(C1)-FIND(“(“, C1)))-1)

to extract the score in parenthesis in C1.

schrick – 🙁

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 ?

Combination of SUM and VLOOKUP (with reference to a second sheet with text/scores in columns next to each other)

scaredy – so where are the scores being created? Where does the numerical value occur on the sheet?

Use IF statements to calculate the score.

So for column E you would have:

=If(E=’Yes”,1,If(E=’No but was cheap”,1,If(E=’No but was expensive”,0,N/A)))

Then use COUNTIF(Range of scores,NOT(ISNA())) to work out the applicable criteria {I can quite remember the format of that one}

Sorry dude it was meant in a playful manner, I guess I should have added a 😛 or a 😆 or just STFU and typed nothing.

Sigh. Give me 5 minutes, I’ll do it…

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.

OK, thanks chaps – investigating vlookup now

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 ?

Is that a goer ?

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.

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

Scaredy, ygm

oooooOOOOOOOooooo !

cheers ‘pig – just got to work out WTF it is now*

*(kidding)**

**(largely)

stoner, I have a thing on my desk that somebody sent me once. It extends its own chart ranges and reformats things whenever it feels like it

I have nearly been burnt at the stake when people see that – there’s only about 2 of us that even know what pivot tables are

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