LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
external usenet poster
 
Posts: 9
Default 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?
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
improve formula, Possible Genious needed !!! Corey Excel Worksheet Functions 8 November 9th 06 10:57 PM
efficiency: database functions vs. math functions vs. array formula nickname Excel Discussion (Misc queries) 2 July 14th 06 04:26 AM
improve formula offset and indirect John Contact Excel Worksheet Functions 1 June 17th 05 07:28 AM
How can improve this formula? Metallo Excel Worksheet Functions 7 January 27th 05 05:49 PM
Need to improve a formula Brian Excel Worksheet Functions 2 December 9th 04 07:17 PM


All times are GMT +1. The time now is 02:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"