shorter formula / not array?: every other col..
 

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))

shorter formula / not array?: every other col..
 

try:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,SUMPRO DUCT(--(MOD(COLUMN($AE4:$AW4),2)=1),$AE4:$AW4,$AF4:$AX4))-AC4))


"nastech" wrote:

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))

shorter formula / not array?: every other col..
 

what do you want done when AF4<""?

"nastech" wrote:

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))

shorter formula / not array?: every other col..
 

hi, hard to see, but it's there, next item after AB4*AE4,(AE4*AF4)+any other
cells that happended to be filled, works accurately as it is; trying
previous posting, here, out. thanks, will check back if that does not work.

"bj" wrote:

what do you want done when AF4<""?

"nastech" wrote:

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))

shorter formula / not array?: every other col..
 

that is too cool.. never would have got that, didn't know column was a
function.. was able to add/del columns.. thanks

"Toppers" wrote:

try:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,SUMPRO DUCT(--(MOD(COLUMN($AE4:$AW4),2)=1),$AE4:$AW4,$AF4:$AX4))-AC4))


"nastech" wrote:

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))

shorter formula / not array?: every other col..
 

hi, looked up / understand some of formula, but have problem with applying to
another column, am I missing something with neg signs: -- ?
can you help with this formula? (is not working) thanks

=IF(R4="","",SUMPRODUCT(--(MOD(COLUMN($R4:$Z4),2)=1),$R4:$Z4,$S4:$AA4))

=IF(R4="","",(R4*S4)+(T4*U4)+(V4*W4)+(X4*Y4)+(Z4*A A4))

"Toppers" wrote:

try:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,SUMPRO DUCT(--(MOD(COLUMN($AE4:$AW4),2)=1),$AE4:$AW4,$AF4:$AX4))-AC4))


"nastech" wrote:

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))

shorter formula / not array?: every other col..
 

IF(R4="","",SUMPRODUCT(--(MOD(COLUMN($R4:$Z4),2)=0),$R4:$Z4,$S4:$AA4))

You need to know whether the first column i,e. R in this case, is odd or
even. R is even so the test needs to be changed as above.

"nastech" wrote:

hi, looked up / understand some of formula, but have problem with applying to
another column, am I missing something with neg signs: -- ?
can you help with this formula? (is not working) thanks

=IF(R4="","",SUMPRODUCT(--(MOD(COLUMN($R4:$Z4),2)=1),$R4:$Z4,$S4:$AA4))

=IF(R4="","",(R4*S4)+(T4*U4)+(V4*W4)+(X4*Y4)+(Z4*A A4))

"Toppers" wrote:

try:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,SUMPRO DUCT(--(MOD(COLUMN($AE4:$AW4),2)=1),$AE4:$AW4,$AF4:$AX4))-AC4))


"nastech" wrote:

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))

shorter formula / not array?: every other col..
 

or to make it generic ...

=IF(S4="","",SUMPRODUCT(--(MOD(COLUMN($R4:$Z4),2)=MOD(COLUMN($R4),2)),$R4:$Z 4,$S4:$AA4))

"Toppers" wrote:

IF(R4="","",SUMPRODUCT(--(MOD(COLUMN($R4:$Z4),2)=0),$R4:$Z4,$S4:$AA4))

You need to know whether the first column i,e. R in this case, is odd or
even. R is even so the test needs to be changed as above.

"nastech" wrote:

hi, looked up / understand some of formula, but have problem with applying to
another column, am I missing something with neg signs: -- ?
can you help with this formula? (is not working) thanks

=IF(R4="","",SUMPRODUCT(--(MOD(COLUMN($R4:$Z4),2)=1),$R4:$Z4,$S4:$AA4))

=IF(R4="","",(R4*S4)+(T4*U4)+(V4*W4)+(X4*Y4)+(Z4*A A4))

"Toppers" wrote:

try:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,SUMPRO DUCT(--(MOD(COLUMN($AE4:$AW4),2)=1),$AE4:$AW4,$AF4:$AX4))-AC4))


"nastech" wrote:

hi, don't know if easier way to setup a formula for saving space & making
more dynamic for adding / deleting columns, but have formula using sets of 2
colums in an equation for price and quantity. doing by hand makes a long -
unwieldy equation. is there a formula or method to overcome? thanks

example using is:

=IF(OR(E4<"",AE4=""),"",(IF(AF4="",AB4*AE4,(AE4*A F4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*A P4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))