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

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

one way:

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

another:

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



In article ,
(cheski) wrote:

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

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

Oops, that should be -

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

Gary

Hi

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


Arvi Laanemets


"GaryDK" wrote in message
ups.com...
Oops, that should be -

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

Gary

What if e=3 and d=7

"JE McGimpsey" wrote:

one way:

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

another:

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



In article ,
(cheski) wrote:

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

Simple:

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

or

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

In article ,
snn wrote:

What if e=3 and d=7

"JE McGimpsey" wrote:

one way:

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

another:

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