simplify formula

[Gary Keramidas]

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

 

[bobbo]

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

 

[salut]

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

 

[JLatham]

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.

 

[Bob Phillips]

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


--

HTH

Bob Phillips

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

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

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

 

[JLatham]

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

 

[Gary Keramidas]

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

--


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]

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

--


Gary

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


--

HTH

Bob Phillips

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