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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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*AF4)+(AG4*AH4)+(AI4*AJ4)+(AK4*AL4)+(AM4*AN4)+(AO4*AP4)+(AQ4*AR4)+(AS4*AT4)+(AU4*AV4)+(AW4*AX4))-AC4))
 
try:

=IF(OR(E4<>"",AE4=""),"",(IF(AF4="",AB4*AE4,SUMPRODUCT(--(MOD(COLUMN($AE4:$AW4),2)=1),$AE4:$AW4,$AF4:$AX4))-AC4))
 
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.
 
that is too cool.. never would have got that, didn't know column was a
function.. was able to add/del columns.. thanks
 
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*AA4))
 
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.
 
or to make it generic ...

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