OR function

[simpson]

Is there an or function?
I need to add the lines in the sampleas inducated but not if the row number is the same as the previous row. If it is the same I want the line total to be 0. and the total for both lines to show in the total of the first. is this possible? The sample is part of a massive workbook so.....sorry so primitive.

[darkyam]

Try =IF(B2=B1,"",SUMPRODUCT((B:B=B2)*(D:H))). Another, more efficient option is to have the sum columns as you have them, then use =IF(B2=B1,"",SUMIF(B:B,B2,K:K)) and hide K. Note that if you want this to be compatible with Excel 2003 or before, you'll have to designate actual ranges for Sumproduct, rather than full column ranges.

[simpson]

great! that works. got one more for ya.
In column M i need the larger of the values in the two lines with the same number in B to stay and the lower of the two to become 0.

[DonkeyOte]

It might help if you specify how the values in Col M are calculated in the first instance...

[darkyam]

You can use =IF(MAX((M:M)*(B:B=B2))=M2,M2,""), confirmed with Control+Shift+Enter, but I have a question: will there always be only one or two rows with the same number in B? Array and sumproduct formulas are pretty inefficient, so if you have a large workbook, they could slow it down considerably, particularly if they have to look at full columns as ranges.

[simpson]

There is "almost" always a pair in column B, but not always. Column M gets its number by refrencing a group of cells on aother page of the workbook. The workbook is about 36 pages totaling about 350K. You've been very helpful. Maybe it would be best to let you see the whole thing and you could decide if there is a better way to avoid the double number entirely.

[darkyam]

If there are never more than two rows with the same number in B and they are all sorted by B, you can use =IF(MAX(IF(B1=B2,M1,0),M2,IF(B3=B2,M3,0))=M2,M2,"") in N. It will be far faster than the array formula I'd given you earlier. The SUMIF for column K should still be relatively efficient, but let us know if your workbook is impossibly slow and we can see if we can speed it up a bit.

[DonkeyOte]

The point is this...

If the values in M are generated by formulae and you wish to amend the value based on the result of the formula then obviously that is a circular calculation.

On that basis your best option would be to use another column independent of M which is based on M and generates your desired output...
(or if easier [dependencies] persist with M as holding final values but store those calcs currently in M in another column and in M reference that other column)

Alternatively you will need to build in the 0/value element into the existing formulae, how trivial that would be would depend largely on the calculation to which we've not as yet been exposed.

I would suggest the first option...

[simpson]

You might be right. I am going to look at hiding the colum and referencing it to get my desired values.
thanks.

[DonkeyOte]

OK - darkyam's offered a few possibilities for creating the "M" values based on the original results...

Another option if the data is sorted by Column B would be:

Please Login or Register  to view this content.

in the above I copied your original M values to Col N and generated M based off N... if that makes sense ?

The above might be worthwhile if you can have lots of records per value in B but want to avoid Arrays... again though it is entirely dependent upon Col B being sorted in Ascending order.

[simpson]

Got it! thanks again!