is it possible to simplify this formula, with sum product or some other means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao
=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary

i think that you could do this.

keep the first formula the same and then

=if(($AB$1 - SUM($AC$1:AD1))72,72,($AB$1 - SUM($AC$1:AD1))

and then copy the formula to column AO


Gary Keramidas wrote:
is it possible to simplify this formula, with sum product or some other means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao
=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary

=MIN($AB$1 - SUM($AC$1:AD1),72)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"bobbo" wrote in message
oups.com...
i think that you could do this.

keep the first formula the same and then

=if(($AB$1 - SUM($AC$1:AD1))72,72,($AB$1 - SUM($AC$1:AD1))

and then copy the formula to column AO


Gary Keramidas wrote:
is it possible to simplify this formula, with sum product or some other
means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao

=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1
-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary

thanks bob, never thought of doing it that way with the min function. appears to
work fine.

--


Gary


"Bob Phillips" wrote in message
...
=MIN($AB$1 - SUM($AC$1:AD1),72)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"bobbo" wrote in message
oups.com...
i think that you could do this.

keep the first formula the same and then

=if(($AB$1 - SUM($AC$1:AD1))72,72,($AB$1 - SUM($AC$1:AD1))

and then copy the formula to column AO


Gary Keramidas wrote:
is it possible to simplify this formula, with sum product or some other
means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao

=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1
-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary

thanks, this is what i was working on and with your help got the result i needed

--


Gary


"bobbo" wrote in message
oups.com...
i think that you could do this.

keep the first formula the same and then

=if(($AB$1 - SUM($AC$1:AD1))72,72,($AB$1 - SUM($AC$1:AD1))

and then copy the formula to column AO


Gary Keramidas wrote:
is it possible to simplify this formula, with sum product or some other
means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao
=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary

Try to use Sum() instead of listing all the cells in the formula.

"Gary Keramidas" wrote:

is it possible to simplify this formula, with sum product or some other means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao
=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary

Gary, I'm not sure if your IF() formulas are going across the sheet into
columns or down the sheet in a single column. I think bobbo has given a
solution that will work if you are placing them across the sheet, as in cells
right under the ones in Row 1.

But if you are running the formulas down the sheet in a single row, then
this would work:
=IF($AB$1-(SUM(OFFSET($AC$1,0,1,1,ROW($AB2)-ROW($AB$2)+1)))72,72,$AB$1-SUM(OFFSET($AC$1,0,1,1,ROW($AB2)-ROW($AB$2)+1)))

The only thing you need to change is the ROW() references, change the value
2 in the very first one to be the value of the row you enter the first
formula into. As set up, that can even be in row 1.

"Gary Keramidas" wrote:

is it possible to simplify this formula, with sum product or some other means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao
=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary

Should have read as
"...down the sheet in a single Column..."


"JLatham" wrote:

Gary, I'm not sure if your IF() formulas are going across the sheet into
columns or down the sheet in a single column. I think bobbo has given a
solution that will work if you are placing them across the sheet, as in cells
right under the ones in Row 1.

But if you are running the formulas down the sheet in a single row, then
this would work:
=IF($AB$1-(SUM(OFFSET($AC$1,0,1,1,ROW($AB2)-ROW($AB$2)+1)))72,72,$AB$1-SUM(OFFSET($AC$1,0,1,1,ROW($AB2)-ROW($AB$2)+1)))

The only thing you need to change is the ROW() references, change the value
2 in the very first one to be the value of the row you enter the first
formula into. As set up, that can even be in row 1.

"Gary Keramidas" wrote:

is it possible to simplify this formula, with sum product or some other means?
by the time i replicate it to column ao, it gets very long.

ab1 = 256

here are the results and formulas for the next 3 columns

ad1 = 96
=IF((AB1-AC1)72,72,AB1-AC1)

ae1 =96
=IF((AB1-AC1-AD1)72,72,(AB1-AC1-AD1))

af1=66
=IF((AB1-AC1-AD1-AE1)72,72,(AB1-AC1-AD1-AE1))

all the way down to column ao
=IF((AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1)72,72,(AB1-AC1-AD1-AE1-AF1-AG1-AH1-AI1-AJ1-AK1-AL1-AM1-AN1))


--


Gary