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

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Dec 12, 2014

• #2

=if(q35="y1",2,if(q35="y2",4,if(q35="y3",6...........etc

 

Upvote 0

yesterdays

Active Member

Joined

Oct 8, 2014

Messages

337

• Dec 12, 2014

• #3

I doesn't get why you have to use SUM
the formula:
=SUM(IF(Q35="y1","2","0"))
will return the same result as :
=IF(Q35="y1","2","0")

Are you referring to some thing else ?

 

Upvote 0

K

Kirsty Cretney

New Member

Joined

Dec 12, 2014

Messages

8

• Dec 12, 2014

• #4

Thanks for the replies. I currently have this -

=IF(Q35="y1",2,IF(Q35="y2",4,IF(Q35="y3",6,IF(Q35="y4",8) it's not working

I've tried it with this - =IF((Q35="y1",2)IF(Q35="y2")4IF(Q35="y3",6)IF(Q35="y4",8)) doesn't work

Please help, I have no excel knowledge what so ever.

Thanks

 

Upvote 0

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Dec 12, 2014

• #5

see post #2

in what way does it not work

 

Upvote 0

P

pjmorris

Well-known Member

Joined

Aug 2, 2012

Messages

2,159

Office Version

1. 365

Platform

1. Windows

• Dec 12, 2014

• #6

why not try this:

=right(Q35,1)*2

A different tack, but at least returns the answers you propose.

and to make you're own formula work you could use this: =IF(Q35="y1",2,IF(Q35="y2",4,IF(Q35="y3",6,IF(Q35="y4",8,""))))

 

Last edited: Dec 12, 2014

Upvote 0

K

Kirsty Cretney

New Member

Joined

Dec 12, 2014

Messages

8

• Dec 12, 2014

• #7

Thanks, finally got it to work, full calc =IF(Q35:AC35="y1",2,IF(Q35:AC35="y2",4,IF(Q35:AC35="y3",6,IF(Q35:AC35="y4",8,IF(Q35:AC35="n1",-2,IF(Q35:AC35="n2",-4,IF(Q35:AC35="n3",-6,IF(Q35:AC35="n4",-8))))))))

Another question, if the cell has a 0 in it can I add this into the formula as well? I tried =IF(Q35:AC35="y1",2,0,IF - but it says there are too many arguments

 

Upvote 0

P

pjmorris

Well-known Member

Joined

Aug 2, 2012

Messages

2,159

Office Version

1. 365

Platform

1. Windows

• Dec 12, 2014

• #8

Try this simplified version:

=IF(LEFT(Q35,1)="N",-1,1) * RIGHT(Q35,LEN(Q35)-1)*2

(I think the Q35:AC35 only returns the top left cell value so the :AC35 isn't needed)

 

Upvote 0

K

Kirsty Cretney

New Member

Joined

Dec 12, 2014

Messages

8

• Dec 12, 2014

• #9

pjmorris said:

Try this simplified version:

=IF(LEFT(Q35,1)="N",-1,1) * RIGHT(Q35,LEN(Q35)-1)*2

(I think the Q35:AC35 only returns the top left cell value so the :AC35 isn't needed)

Click to expand...

Hi Peter

I'm sorry I don't get the above, I need the formula to calculate across 12 columns, and for them possibly to add in a number of different scores but also some of them are going to be blank. I need it to autocalculate to ensure the figures I pull are correct and it'll be easier for the team members.

So from column Q-AC I need them to be able to put in either Y1 for 2 points and n1 to -2 points but the field might also be N/A or 0 or blank whichever is easiest for the formula.

As I said I'm not Excel wise at all so getting confused.

Many Thanks

 

Upvote 0

P

pjmorris

Well-known Member

Joined

Aug 2, 2012

Messages

2,159

Office Version

1. 365

Platform

1. Windows

• Dec 12, 2014

• #10

Hi Kirsty,

I'm confused. I can only make your formula work if I enter it as an Array formula: press CTRL-SHFT-Enter to enter the formula and it then has {} at either end. If I don't do this it returns #value.

Can you provide a sample row of data with the expected output - I think this is entirely doable, but need more detail.

Regards

 

Upvote 0