| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
My formula is too long, What am I to do?
My formula given below needs to be modified and excel will not allow me to
add digits. Is there another way to write this formula that would be more concise or can I extent the capacity of certain cells? Scott Miller University of Washington =SUMPRODUCT(--(DATA!$C$2:$C$1500=98061),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98340),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98342),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98345),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98346),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98364),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98370),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98392),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5)) -- Scott Miller University of Washington Chemistry |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
My formula is too long, What am I to do?
Worked great! Should have learned that one along time ago. Great help!
-- Scott Miller University of Washington Chemistry "Ron Coderre" wrote: Try this: First, put your list of 1st match values in a vertical list. I'll assume they're in A1:A11.... A1: 98061 A2: 98110 etc (make sure they're in ascending order for easier checking) Then, use this formula: =SUMPRODUCT(--ISNUMBER(MATCH(C2:C1500,A1:A11,0)),--(AB2:AB15000),--(AB2:AB1500<=5)) Does that help? *********** Regards, Ron "Scott" wrote: My formula given below needs to be modified and excel will not allow me to add digits. Is there another way to write this formula that would be more concise or can I extent the capacity of certain cells? Scott Miller University of Washington =SUMPRODUCT(--(DATA!$C$2:$C$1500=98061),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98340),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98342),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98345),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98346),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98364),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98370),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98392),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5)) -- Scott Miller University of Washington Chemistry |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
My formula is too long, What am I to do?
Try this:
First, put your list of 1st match values in a vertical list. I'll assume they're in A1:A11.... A1: 98061 A2: 98110 etc (make sure they're in ascending order for easier checking) Then, use this formula: =SUMPRODUCT(--ISNUMBER(MATCH(C2:C1500,A1:A11,0)),--(AB2:AB15000),--(AB2:AB1500<=5)) Does that help? *********** Regards, Ron "Scott" wrote: My formula given below needs to be modified and excel will not allow me to add digits. Is there another way to write this formula that would be more concise or can I extent the capacity of certain cells? Scott Miller University of Washington =SUMPRODUCT(--(DATA!$C$2:$C$1500=98061),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98340),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98342),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98345),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98346),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98364),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98370),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98392),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5)) -- Scott Miller University of Washington Chemistry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Streamlining a long IF=(AND formula ?? | Excel Discussion (Misc queries) | |||
| formula too long | Excel Worksheet Functions | |||
| more than 3 conditions in conditional formatting - possible? | Excel Discussion (Misc queries) | |||
| long formula obscures cell view | Excel Discussion (Misc queries) | |||
| Formula too long | Excel Worksheet Functions | |||
Linear Mode
