Hi All, Trying to get a formaula to calculate multiple calculations

[Kirsty Cretney]

I currently have the following =SUM(IF(Q35="y1","2","0")) which works fine but the problem is I need to do the same with all of the following -

If Y2 then +4
If Y3 then +6
If Y4 then +8

And just to make it that little bit more difficult I then need If N1 then -2 and so on. Is this possible or am I living in a dream?

 

[Kirsty Cretney]

Do you have an email address I could forward my spreadsheet to?

Thanks

 

[pjmorris]

I'm doing this at work - so probably ought not share e-mail address!!

From what you've stated so far I imagine a row of data might look like this:

Q2 R2 S2 T2 U2 V2 W2 X2 Y2 Z2 AA2 AB2 AC2
Y1 "" "" Y2 "" "" "" "" "" "" "" Y1 ""

But I'm not sure what output you would expect.

 

[Darren Bartrup]

How about something like:
=CHOOSE(MID(Q35,2,LEN(Q35)),2,4,6,8)

MID(Q35,2,LEN(Q35)) will return the characters after the first digit - so Y1 will return 1, etc.

 

[Kirsty Cretney]

Yeah, Y2 = 4 points N2 = -4 points N/A = 0

I have 13 questions that they could put one of the above in the box. I need to calculate the total number of points available, no + points and no of - points.


I was going to attach an extract of my sheet but it wont let me, it's harder to explain than it actually is.


Thanks

 

[pjmorris]

I think I get it.

Try this:

=SUMPRODUCT(--(LEFT(Q35:AC35,1)="Y"),IFERROR(RIGHT(Q35:AC35,LEN(Q35:AC35)-1),0)*2)-SUMPRODUCT(--(LEFT(Q35:AC35,1)="N"),IFERROR(RIGHT(Q35:AC35,LEN(Q35:AC35)-1),0)*2) enter it by pressing CTRL-SHFT-Enter, it should then look like this, but note that you cannot enter the curly braces manually:

{=SUMPRODUCT(--(LEFT(Q35:AC35,1)="Y"),IFERROR(RIGHT(Q35:AC35,LEN(Q35:AC35)-1),0)*2)-SUMPRODUCT(--(LEFT(Q35:AC35,1)="N"),IFERROR(RIGHT(Q35:AC35,LEN(Q35:AC35)-1),0)*2)}

You can then copy and paste it down the rows you need.

Hope this works, it did in my test. (Just noticed that it doesn't like N/A, so recommend simply leaving a blank for those questions for which no reply is applicable).

 

[Kirsty Cretney]

What does the Right/Left/ Len stand for?

I just put it in and it's a no go.

 

[pjmorris]

The first part of each SUMPRODUCT, for example: (--(LEFT(Q35:AC35,1)="Y"), compares the left most character of each cell against the letter Y. The structure of the Left Function is LEFT(String, NumCharacters). This returns an array of True or False values, when this is multiplied by -- (a double negative) it forces the True and False values to 1 or 0.

The second part is attempting to isolate the number part of your strings. So its structure is RIGHT(STRING, NumCharacters), but since we don't know how many characters are in the number part it could be 1 or 2, but measuring the length of the string LEN(String) and subtracting 1 (to allow for the letter at the start) we get just the number. The problem here is that the Strings might be blank spaces and we then get RIGHT("", -1) which not surprisingly confuses the RIGHT Function as it doesn't know what to do. This is tested in the IFERROR part and simply returns a 0 if there was an error. It is this part which requires the formula to be entered with CTRL-SHFT-ENTER.

The formula is expecting your replies to be a single character 'Y' or 'N' followed by a number. It must have no other characters or spaces, if it does it will fail.

Finally, please be absolutely certain that you have pressed CTRL (and hold it down), press SHIFT (and hold it down) and finally press ENTER (when you press Enter you must have the Control Key and the Shift Key down at the same time!), this will put the curly braces { & } around the formula. Make you sure you've copied the formula in Bold above into the line before entering it.

Y10

N5

=

10

<tbody>
</tbody>

If this doesn't work I'm stumped as I can only assume your data is different to that described above.

Regards

 

[Kirsty Cretney]

Thanks for all your help, I will try and see how I get on on monday.

If it doesn't work then I am obviously doing something wrong and they can calculate the thing themselves manually.

Thank you

 

[pjmorris]

Likewise - I'm about to disappear.

Perhaps over the weekend (or a different timezone) someone will come up with a better answer - I don't like using array formulas.

Hope I didn't labour the point about how to enter the formula.

Have a good weekend.