ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   simplify formula (https://www.excelbanter.com/excel-programming/375878-simplify-formula.html)

Gary Keramidas
simplify formula
 
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

bobbo
simplify formula
 
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

salut
simplify formula
 
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

JLatham
simplify formula
 
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

Bob Phillips
simplify formula
 
=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

JLatham
simplify formula
 
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

Gary Keramidas
simplify formula
 
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

Gary Keramidas
simplify formula
 
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


All times are GMT +1. The time now is 04:50 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com