Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
I have a SUM array formula that has multiple nested IF statements,
making it very inefficient. My formula spans over 500 rows, but here is a simple version of it: {=SUM(IF(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170, IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17,0))} As you can see, the first half of the formula checks where the array is greater than zero, and if they are, it sums those in the second part of the formula. You will notice that the same IF statement is repeated in there twice, which to me is inefficient, but is the only way I could get the correct answer. The example data I have is as follows: Row Col A 1 120 2 120 3 120 4 120 5 120 6 7 100 8 100 9 100 10 100 11 100 12 13 50 14 50 15 50 16 50 17 50 The answer should be 350 in this instance using the formula I mentioned above. If I tried to put in a MAX statement within the array, therefore removing the test to find where it was greater than zero, so it was like this: {=SUM(MAX(IF(B2:B6B8:B12,B2:B6,B8:B12)-B14:B18,0))} However, it seems like it only calculates the first row of data in each range, and it gave me the wrong answer of 70. Does anyone know a away that I can reduce the size of the formula or make it more efficient by not needing to repeat an IF statement in there? |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
Hi,
You might consider an SUMPRODUCT function such as: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17)) If this helps, please click the Yes button Cheers, Shane Devenshire "Anthony" wrote: I have a SUM array formula that has multiple nested IF statements, making it very inefficient. My formula spans over 500 rows, but here is a simple version of it: {=SUM(IF(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170, IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17,0))} =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17)) As you can see, the first half of the formula checks where the array is greater than zero, and if they are, it sums those in the second part of the formula. You will notice that the same IF statement is repeated in there twice, which to me is inefficient, but is the only way I could get the correct answer. The example data I have is as follows: Row Col A 1 120 2 120 3 120 4 120 5 120 6 7 100 8 100 9 100 10 100 11 100 12 13 50 14 50 15 50 16 50 17 50 The answer should be 350 in this instance using the formula I mentioned above. If I tried to put in a MAX statement within the array, therefore removing the test to find where it was greater than zero, so it was like this: {=SUM(MAX(IF(B2:B6B8:B12,B2:B6,B8:B12)-B14:B18,0))} However, it seems like it only calculates the first row of data in each range, and it gave me the wrong answer of 70. Does anyone know a away that I can reduce the size of the formula or make it more efficient by not needing to repeat an IF statement in there? |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
On Nov 25, 11:06*am, Shane Devenshire
wrote: Hi, You might consider an SUMPRODUCT function such as: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--(A1:A5A7:A11,A1:*A5,A7:A11)-A13:A17)) If this helps, please click the Yes button Cheers, Shane Devenshire Hi Shane I tried your suggestion, but it just gives a #VALUE error (even if i press Ctrl Shift Enter to make it an array formula) Anthony |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
Hi,
Didn't test, try this instead: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17) Array entered. I don't know if this is the result you want but with dummy data I get a result. Cheers, Shane Devenshire "Anthony" wrote: On Nov 25, 11:06 am, Shane Devenshire wrote: Hi, You might consider an SUMPRODUCT function such as: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--(A1:A5A7:A11,A1:Â*A5,A7:A11)-A13:A17)) If this helps, please click the Yes button Cheers, Shane Devenshire Hi Shane I tried your suggestion, but it just gives a #VALUE error (even if i press Ctrl Shift Enter to make it an array formula) Anthony |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
On Nov 25, 11:51*am, Shane Devenshire
wrote: Hi, Didn't test, try this instead: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--IF(A1:A5A7:A11,A*1:A5,A7:A11)-A13:A17) Array entered. *I don't know if this is the result you want but with dummy data I get a result. Cheers, Shane Devenshire "Anthony" wrote: On Nov 25, 11:06 am, Shane Devenshire wrote: Hi, You might consider an SUMPRODUCT function such as: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--(A1:A5A7:A11,A1:**A5,A7:A11)-A13:A17)) If this helps, please click the Yes button Cheers, Shane Devenshire Hi Shane I tried your suggestion, but it just gives a #VALUE error (even if i press Ctrl Shift Enter to make it an array formula) Anthony- Hide quoted text - - Show quoted text - Shane That works, but i think i have found an even more efficient method: =MAX( MAX( sum(A1:A5), sum(A7:A11) ) - sum(A13:A17), 0) It seems to work OK from my initial tests. Anthony |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
Hi,
I don't know how much this will improve the efficiency but if the range unchanging you can define a name, say T which is equal to =IF($A$1:$A$5$A$7:$A$11,$A$1:$A$5,$A$7:$A$11)-$A$13:$A$17 then you spreadsheet formula becomes =SUMPRODUCT(--(T0),T) which does not need to be arry entered. If you want the ranges to be relative then naming the range is a little tricker, select the cell where you are going to enter the formula and then choose Insert, Name, Define, enter T as the name and in the Refers to box enter =IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17 The formula in the spreadsheet is the same as above. I also notice that even if you don't consider any of these ideas you can simplify the original suggestion to =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17) All that's changed is the -- in the second part of the formula. It still needs to be entered as an array. Cheers, Shane Devenshire "Shane Devenshire" wrote: Hi, Didn't test, try this instead: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17) Array entered. I don't know if this is the result you want but with dummy data I get a result. Cheers, Shane Devenshire "Anthony" wrote: On Nov 25, 11:06 am, Shane Devenshire wrote: Hi, You might consider an SUMPRODUCT function such as: =SUMPRODUCT(--(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170),--(A1:A5A7:A11,A1:Â*A5,A7:A11)-A13:A17)) If this helps, please click the Yes button Cheers, Shane Devenshire Hi Shane I tried your suggestion, but it just gives a #VALUE error (even if i press Ctrl Shift Enter to make it an array formula) Anthony |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
On Nov 24, 7:39*pm, Anthony wrote:
That works, but i think i have found an even more efficient method: =MAX( MAX( sum(A1:A5), sum(A7:A11) ) - sum(A13:A17), 0) It seems to work OK from my initial tests. It certainly is not the same as your original array formula in general, unless there is something about your data that you have not explained. Consider the following random data: Row Data 1 114 2 144 3 126 4 142 5 182 6 7 172 8 100 9 120 10 100 11 100 12 13 50 14 50 15 50 16 50 17 50 |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
On Nov 24, 3:42*pm, Anthony wrote:
My formula spans over 500 rows, but here is a simple version of it: {=SUM(IF(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170, IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17,0))} My interpretation of what you want is: =MAX(A1-A13,A7-A13,0) + MAX(A2-A14,A8-A14,0) + MAX(A3-A15,A9-A15,0) + MAX(A4-A16,A10-A16,0) + MAX(A5-A17,A11-A17,0) I'm not suggesting that as a solution, of course. It's just a conceptual module. More generally: =sumproduct(max(A1:A5-A13:A17, A7:A11-A13:A17, 0)) That does not work as intended, of course. Again, just a conceptual model. I had hoped to make SUBTOTAL work, but I have not succeeded. |
#9
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Trying to improve efficiency of array formula
On Nov 25, 6:34*pm, joeu2004 wrote:
On Nov 24, 3:42*pm, Anthony wrote: My formula spans over 500 rows, but here is a simple version of it: {=SUM(IF(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170, * * * IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17,0))} My interpretation of what you want is: =MAX(A1-A13,A7-A13,0) + MAX(A2-A14,A8-A14,0) + *MAX(A3-A15,A9-A15,0) + MAX(A4-A16,A10-A16,0) + *MAX(A5-A17,A11-A17,0) I'm not suggesting that as a solution, of course. *It's just a conceptual module. *More generally: Yes, your conceptual model is correct, however i need to build an array formula that would give the same result. Anthony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
improve formula, Possible Genious needed !!! | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
improve formula offset and indirect | Excel Worksheet Functions | |||
How can improve this formula? | Excel Worksheet Functions | |||
Need to improve a formula | Excel Worksheet Functions |