Incremental balance and progress bar

[kim]

Hi
Trying to do a weakest link type game for kids.
There are 9 questions and a progress bar (thermometer type)
if question answer is correct £100 is added and the bar moves up a step. If
the answer is incorrect they lose £100 and the bar goes down 100.
I tried to set this up various ways using IF forrmula, then Macros attached
to a graphic button , and feeding into a conditional format cell shading to
increase the thermometer bar in steps. I got the conditional format to work
with a simple cell entry but can't figure how to progress it to do what I need
None work. I think it may need vB code which I know very little about. (or
a simple way to do it I don't know about!)
Could a kind person help me out?
Thanks
This is driving me nuts!

 

[Bernard Liengme]

Let's say the questions are in A1:100; answers in B1:B100
In C1:C100 we have Y for correct and N for wrong (or someother code)
In D1 we have =COUNTIF(C1:C100,"Y")*100 to count correct answers and
multiply by 100
Then we need to make a 'thermometer chart' from the data in D1
See http://www.andypope.info/charts/thermometer.htm
best wishes

 

[kim]

Thanks that was great got it working but need 2 more jobs!
1. If the answer is wrong I need to subtract £100 tried nesting a function
but didn't work. a variation of If "N" then -100.
2. At any time the player can Bank their current total so this value is
stored in a cell labelled bank and the totals column is cleared haven't a
clue how to do that bit!
Thank you for your help

 

[Bernard Liengme]

=(COUNTIF(C1:C100,"Y")-COUNTIF(C1:C100,"N"))*100
If neg values not allowed
=MAX(0,(COUNTIF(C1:C100,"Y")-COUNTIF(C1:C100,"N"))*100 )
Not sure about part two and it is late will look on Thursday
best wishes