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
--
LindaM

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

On Jul 22, 10:53*am, LindaM wrote:
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
--
LindaM

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*S UM(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.
--
David Biddulph

"LindaM" wrote in message
...
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
--
LindaM

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

--
__________________________________
HTH

Bob

"LindaM" wrote in message
...
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
--
LindaM

Typo

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

--
__________________________________
HTH

Bob

"Bob Phillips" wrote in message
...
=SUMPRODUCT(ROW(INDIRECT("4:10"))*2.73)+SUMPRODUCT (ROW(INDIRECT("11:"&A1)))*1.75

--
__________________________________
HTH

Bob

"LindaM" wrote in message
...
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
--
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.

--
LindaM


"Pete_UK" wrote:

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

On Jul 22, 10:53 am, LindaM wrote:
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
--
LindaM

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

"LindaM" wrote:

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.

--
LindaM


"Pete_UK" wrote:

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

On Jul 22, 10:53 am, LindaM wrote:
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
--
LindaM

Sorry, should have been
=2.43*SUM(ROW(A4:INDIRECT("A"&MIN(10,A1))))+1.75*S UM(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.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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*S UM(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.
--
David Biddulph

"LindaM" wrote in message
...
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
--
LindaM

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

On Jul 22, 11:53*am, LindaM wrote:
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.

--
LindaM



"Pete_UK" wrote:
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

On Jul 22, 10:53 am, LindaM wrote:
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
--
LindaM- Hide quoted text -

- Show quoted text -

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

--
LindaM


"Mike H" wrote:

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

"LindaM" wrote:

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.

--
LindaM


"Pete_UK" wrote:

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

On Jul 22, 10:53 am, LindaM wrote:
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
--
LindaM