Anyone prepared to translate into exel code?

  tasslehoff burrfoot 19:58 16 Jun 2004

Hi all,

I do not usuallly use exel and therefore have no idea how to do this. I don't even know what's involved, so I apologise if this is hugely time consuming or difficult. Obviously, If this is the case, I do not expect an answer and thank you for your time in reading this.

What I need to do is create an exel file with about 20 questions in it. After each question I need a bit of code to add up a score depending on how the questions are answered, with different questions being worth different points and some being not applicable in certain scenarios. This will mean there being a total which can be attained but will be a different amount from scenario to scenario and an actual total which will be generated by the answers.

Hope that made sense, but not too confident so this is what I need in plain English but I've tried to make it adopt a "codey" look in the hope it will clarify what I mean:

Potential total="0"
Actual total="0"

Question one "sample question"

points value="10"
if question one ="yes" then add 10 to the potential total AND add 10 to the actual total
If question one="no" then add 10 to the potential total BUT keep actual total as it is.
If quesion one="N/A" then both totals remain at their current value

Question 2 "another sample question"

points value="5"
if question two ="yes" then add 5 to the potential total AND add 5 to the actual total
If question two="no" then add 5 to the potential total BUT keep actual total as it is.
If quesion two="N/A" then both totals remain at their current value

and so on up to question 20.

At the end, I need the potential total and the actual total to appear both in numbers and as a percentage (actual/potential).

In typing this it made me realise that this is probable gonne be quite time consuming. If this is the case then I don't expect anyone to waste a lot of time on it so can somebody say it's a big job so I can resolve it and not waste anyone esles time.

Many thanks for reading this.


  spikeychris 20:00 16 Jun 2004

The Vogster will turn up soon wearing his underpants on the outside.

  VoG II 20:04 16 Jun 2004

Put your questions in A1:A20

In B1:B20 put the potential scores for each question. Put the answers in C1:C20. Hide Columns B and C.

User enters answer in Column D.

In E1 enter


and copy the formula down to E20.

wherever you like you can then enter the following formulas:

Tot Pot Score =SUM(B1:B20)

Tot actual score =SUM(E1:E20)

% =100*SUM(E1:E20)/SUM(B1:B20)

  spikeychris 20:12 16 Jun 2004

Told you.

  VoG II 20:15 16 Jun 2004

Done but with errors!

In E1 enter


  tasslehoff burrfoot 20:25 16 Jun 2004

but, when copying the formula down column E, I assume I will have to change the numbers, to reflect the row number?

Any idea for the N/A option? Or can this not be done?

As a guess I reckon I could add two columns for the NO and N/a options and alter the formula. (does exel use ELSEIF?)


  VoG II 20:32 16 Jun 2004

When you've entered the formula in E1, hover the cursor over the bottom right of E1 and the cursor will turn into a +

Hold down the left mouse button and drag down to copy the formula down - the numbers will change automatically.

You can change the formula to


However, if you change it to display Excel's #N/A error value, this will cause the SUM formulas also to return #N/A

  Dorsai 20:33 16 Jun 2004

Not wrong there. Is it a bird...Is it a's Vog (still can't work out how to do the superscript TM bit)

But if it wearn't for the Vog's of this world we'd still be playing 'Manic Miner' on our ZX Spectrums...Now aint that a frighting thought!

  tasslehoff burrfoot 20:35 16 Jun 2004

it changed the numbers automatically.

  tasslehoff burrfoot 20:40 16 Jun 2004

but the totals aren't working. I just get the whole formula you've given in the cell, not the numerical outcome.

Any idead what I've done wrong?

And will this formula exclude a question from the potential total if the user enters N/A?

Thanks again

  VoG II 20:43 16 Jun 2004

I may have misled you with

Tot Pot Score =SUM(B1:B20)

what I meant was to get that total enter the formula


Let's get the totals working first, then we'll worry about N/A.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires