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

[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


"GaryDK" <[email protected]> wrote in message
news:[email protected]...
> Oops, that should be -
>
> =SUM(IF(A2:C2="e",A1:C1,A1:C1*2))
>
> Gary
>

[JE McGimpsey]

one way:

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

another:

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



In article <[email protected]>,
[email protected] (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

[snn]

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 <[email protected]>,
> [email protected] (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
>

[JE McGimpsey]

Simple:

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

or

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

In article <[email protected]>,
snn <[email protected]> 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")))
> >