| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to make a contingent formula shorter... | Excel Discussion (Misc queries) | |||
| Formula too long - Shorter version? | Excel Worksheet Functions | |||
| 2nd attempt ~ Faster/Shorter formula | Excel Worksheet Functions | |||
| Need faster/shorter formula | Excel Worksheet Functions | |||
| Shorter Formula | Excel Discussion (Misc queries) | |||
Linear Mode
