Multiple "If"

[LindaM]

I am trying to write a formula that will multiple the numbers between 4 and
10 by $2.43 and add that to the numbers between 11 and 100 that is multiplied
by $1.75.
Example: A1 = 61 and the total in B1 would be 4 thru 10 multilplied by 2.43
and then added to 11 thru 61 that has been multiplied by 1.75

 

[Pete_UK]

Do you mean that you want 4*2.43 + 5*2.43 + 6*2.43 + 7*2.43 + 8*2.43 +
9*2.43 + 10*2.43 + 11*1.75 + 12*1.75 +13*1.75 etc up to 61*1.75 ? (and
whatever for ?) Or, could it be that you want (10-4)*2.43 +
(61-11)*1.75 ?

Please explain.

Pete

 

[David Biddulph]

One way would be this as an array formula (entered with Control shift
Enter), but there are other ways which may well be simpler.

=2.43*SUM(ROW(A1:INDIRECT("A"&MIN(10,A1))))+1.75*SUM(ROW(A11:INDIRECT("A"&A1)))*(A1>=11)

I'm guessing that you probably want to count only as far as your A1, even if
that is below 11.

 

[Bob Phillips]

=SUMPRODUCT(ROW(INDIRECT("4:10"))*2.73)+SUMPRODUCT(ROW(INDIRECT("11:"&A1)))*1.75

 

[Bob Phillips]

Typo

=SUMPRODUCT(ROW(INDIRECT("4:10"))*2.43)+SUMPRODUCT(ROW(INDIRECT("11:"&A1)))*1.75

 

[LindaM]

I mean the numbers between 4 and 10 which is 7*2.43 added to the numbers
between 11 and the total count up to 100 *1.75. If the total number is 61 it
would be 4-10 = 7 * 2.43 + 11-61=51*1.75. The total amount should equal
$106.25.

 

[Mike H]

Hi,

With 61 in A1 try this, or have I misunderstood?

=MAX(0,(MAX(0,(A1-10)*1.75))+(A1-MAX(0,(A1-10))-3)*2.43)

Mike

 

[David Biddulph]

Sorry, should have been
=2.43*SUM(ROW(A4:INDIRECT("A"&MIN(10,A1))))+1.75*SUM(ROW(A11:INDIRECT("A"&A1)))*(A1>=11)

I'd missed the fact that you were starting from 4, not from 1.

You may want to look at how it behaves, and how you want it to behave, if A1
is less than 4.

 

[Pete_UK]

In that case, Linda, you can try this:

=IF(A1<4,0,IF(A1<11,(A1-3)*2.43,7*2.43+(A1-10)*1.75))

with 61 in cell A1.

Hope this helps.

Pete

 

[LindaM]

Mike you are awesome, thank you so much. I thought that you had to use "If".