Using functions to calculate multiple scores according to their level of difficulty

[cheski]

Dear all,

I would like to do the following:

Let me first show you my table

A B C
(1) 15 10 9
(2) e d e

Row (1) displays scores
Row (2) displays level of difficulty (e=easy, d=difficult)

I would like to make a function that, according to the difficulty
either multiplies the score *1 for easy and *2 for difficult level of
difficulty.

The result would be: 1*15 + 2*10 + 1*9 = 15 + 20 + 9 = 44. This by
itself would not be too hard with an IF-function. However, just
imagine a row that consists of 20 scores and how big the IF function
would be!

Anyone any idea as to how to do it in a faster, more efficient way?

Many thanks in advance!

Kind regards,
Cheski Frank, Switzerland

 

[JE McGimpsey]

one way:

=SUM(A1:C1)+SUMIF(A2:C2,"d",A1:C1)

another:

=SUMPRODUCT(A1:C1,(1+(A2:C2="d")))

 

[GaryDK]

Hi,

If you enter this as an array formula (see help if you don't know about
array formulas), it general syntax should do what you need -

=SUM(IF(A2:C2="e",A1:C1,A1:C1*10))

Gary

 

[GaryDK]

Oops, that should be -

=SUM(IF(A2:C2="e",A1:C1,A1:C1*2))

Gary

 

[Arvi Laanemets]

Hi

Or
=SUM((A1:C1)*(1+(A2:C2="d")))
(entered as an array formula too)


Arvi Laanemets

 

[snn]

What if e=3 and d=7

one way:

=SUM(A1:C1)+SUMIF(A2:C2,"d",A1:C1)

another:

=SUMPRODUCT(A1:C1,(1+(A2:C2="d")))

 

[JE McGimpsey]

Simple:

=3*SUM(A1:C1)+4*SUMIF(A2:C2,"d",A1:C1)

or

=SUMPRODUCT(A1:C1,(3+4*(A2:C2="d")))