Sleeper: Array formula

[Ismael]

Hi to all,

I Have a doubt in a array formula, in column L of the file attach you guys can see that I type a array formula, what I pretend is now how many numbers are in column H between to values (A4 and A5) and the pull down until L24.

So if any of you guys can give me a help I appreciate.

Best Regards,

Ismael

[Zack Barresse]

From what I understand, you could use this formula ..


=SUMPRODUCT(--($I$5:$I$19>=A5),--($J$5:$J$19<=A6))

.. but it will give you the same results. According to your criteria, there will never be a match. Could you explain your criteria a little more in detail?

[Ismael]

Hi,

I will try explain better my doubt.

So the value in cell K5 has correspondence with the cell?s I5 and J5, not in formulas but in data they stay ?together?, so if the value in K5 depend of the values of I5 and J5, and if this values are between A5 and A6 I will count how many values are in this condition on the column I and J.

So the first results must be in cell L5 I will have 1 was result because I only have one value in column K that are between 4:00 and 5:00 on column I and J.

In cell L5 the result will be 10, the value in K5 still count because the value in J5 is 5:40 that ?belong? to the interval between 5:00 and 6:00, then we have the other values that belong to this interval until K13 inclusive.

Then make the same thing for the others cell?s.

I don?t now if I explain well what I pretend, I hope so.

Best regards,

Ismael

[Zack Barresse]

Ismael, I'm sorry but I do not understand. Are the values in column K input by hand?

Let's take one value and you can show me how the logic flows and what cells it must look at. How about L5. Exactly what cells must it look at and what should their values be to be considered counted/summed?

Sorry I don't understand more. :(

[Ismael]

Hi Jake,

Last try.....

In cell L5 we have to make this:

Check how many lines in column I and J are values between the value of A5 and A6 in others words, something like that:

A5 >= How many lines in column I and J < A6

So the result of a possible formula in cell L5 will be 1, because just one line in column I and J are between 4:00 and 5:00, it?s true that the value of J5 is 5:40, but the value in I5 is 4:10 that are inside the interval of 4:00 ? 5:00.

I don?t now if you are understand what I'm trying to do, but it?s simple image that I have intervals (4:00 ? 5:00; 5:00 ? 6:00, until 24:00) this intervals are in hours and they have 1 hour of duration ok?

Now I have the data in the range column G until column J write? This data are flights that have one hour and 30 minutes or 2 hours and 30 minutes as you can see if make column J minus column I. What I want is now how many flights I have in one hour.

I hope that you can understand now and help me.

Regards,


Ismael

[Zack Barresse]

Okay, I think I understand. So this is an OR statement, not an AND statement..? Basically, the one value is between A5 and A6 (4:10) but the other is not (5:40). This means the first one counts as one, yet the latter time does not count as one. Yes? If the second time was between 4:00 and 5:00 it would count as one as well?

Hope I understand you. We'll get it worked out. :yes

[Ismael]

Hi Zack,

Ok now you understand me, but how can I do this whit an array formula???

I try something like that:


SUM(IF(OR($I$5:$I$500>=A5;$J$5:$J$500<A6);$B$5:$K$500))

But didn?t work, so I?m stuck again?

Regards,

Ismael

[Zack Barresse]

=SUM(IF(($I$5:$I$500>=A5)+($J$5:$J$500<A6),$K$5:$K$500))

.. confirmed with Ctrl + Shift + Enter, of course. Or, a non-array entered formula ...


=SUMPRODUCT(--($I$5:$I$500<=A5)+($J$5:$J$500>=A6),$K$5:$K$500)

The key is the + operator, not the * operator. In this type of logic, the * operator means AND whereas the + operator means OR.


HTH

[Ismael]

Hi again Zack,


Sorry but the formula didn?t work as you can see in the file attach, I don?t now why?

Regards,


Ismael

[Zack Barresse]

I do not fully understand what you mean then. I was under the impression that something like this would represent valid data ...


=(I5>=A5)+(J5<=A6)

If this does not represent what you think is valid data, then I am still missing something.

I know that Carlos (Paleo) has helped you out with things in the past and that you both speak Portugese. Would it be easier to make this post in the Non English Help (http://www.vbaexpress.com/forum/forumdisplay.php?f=74) forum?

[Ismael]

Hi Zack,

Ok I will try to post this doubt in the Carlos web site, and see if he can understand better what I pretend. If he answer my question I post back were again ok?

Nerveless I have built a worksheet that does what I want but not in a very intelligent way, but I think if you see the formulas and the steps that I have done maybe you can understand better my doubt. (this is built on columns N to S)

Thanks and regards,

Ismael

[OBP]

Ismael, would you like to do this with Visual basic and a command button instead of formula?

[Ismael]

Hi,

for me it's no problem do this with Visual basic and a command button instead of formula.

if you can help me with that, I realy apreciate.

regards,

Ismael

[OBP]

Ismael, I am not sure that this does exactly what you want. It counts the occurrences of flights from 04:00 to 04:59 and 05:00 to 05:59 etc. Whereas your formula counted from 04:01 to 05:00 and 05:01 to 06:00. So give it a try and see what you think. Copy this code to a command button and click the button, it willwork through the flights in both columns (up to 20000 rows) and count the times as I have described and then put them in column N from N4 downwards and the times represent 00:00 up to 23:00.


Dim flighttime(0 To 23) As Integer, count As Integer
Application.ScreenUpdating = False
For count = 1 To 20000
Range("i4").Select
ActiveCell.Offset(count, 0).Select
If ActiveCell = "" Then Exit For
flighttime(Hour(ActiveCell)) = flighttime(Hour(ActiveCell)) + 1
ActiveCell.Offset(0, 1).Select
flighttime(Hour(ActiveCell)) = flighttime(Hour(ActiveCell)) + 1
Next count
Range("n5").Select
For count = 0 To 23
Range("n5").Select
ActiveCell.Offset(count, 0).Select
ActiveCell = flighttime(count)
Next count
Application.ScreenUpdating = True

[Ismael]

Hi,

I think that your code almost done what I want, but as you can see in the attach the first isn?t correct because I don?t have any flight from 00:00 until 1:00 and your code return 1.

Now just one question, in your code you only do the check on column I true?

The problem is that the value of column I is the begging of the flight and the column J is the end, so I could have fights you don?t count because this write?

Regards,

Ismael

[OBP]

Ismael, this piece of code -

ActiveCell.Offset(0, 1).Select
flighttime(Hour(ActiveCell)) = flighttime(Hour(ActiveCell)) + 1

checks Column J.

I obviously do not quite understand what you are trying to do as the last flight in column J finishes at 00:10 whch is what gives the count of 1 for 00:00 to 00:10.
TP 225 21:40 00:10 4

If you count cloumn "I" do you also count Column "J" as well.

I can see that in your formula you are using the values in Column K but I do not know how you get those values.

[Ismael]

Hi again,

Ok I made reference to the column K because every flight has one or more check-in desk and the values of column K are the number of check-in desk for each flight.

So your code is correct, just need to adjust the number of column k.

For example from 5:00 to 6:00 your code return 11 that's correct because exist 11 flights between this interval but what I pretend is now sum the values of column K, and the result will be 19.

If you can help to adjust the code I really appreciate.

Thanks and regards,

Ismael

[OBP]

So that I understand do you add column K if there is a suitable value in I, or if there is a suitable value in J, but not add it twice if there is a suitable value in I and J?

Ismael, try this -



Dim flighttime(0 To 23) As Integer, count As Integer
Application.ScreenUpdating = False
For count = 1 To 20000
Range("i4").Select
ActiveCell.Offset(count, 0).Select
If ActiveCell = "" Then Exit For
flighttime(Hour(ActiveCell)) = flighttime(Hour(ActiveCell)) + 1
flighttime(Hour(ActiveCell)) = flighttime(Hour(ActiveCell)) + ActiveCell.Offset(0, 2).Value 'added for column K
ActiveCell.Offset(0, 1).Select
flighttime(Hour(ActiveCell)) = flighttime(Hour(ActiveCell)) + 1
flighttime(Hour(ActiveCell)) = flighttime(Hour(ActiveCell)) + ActiveCell.Offset(0, 1).Value 'added for column K
Next count
Range("n5").Select
For count = 0 To 23
Range("n5").Select
ActiveCell.Offset(count, 0).Select
ActiveCell = flighttime(count)
Next count
Application.ScreenUpdating = True

[Ismael]

Hi, OBP

Thanks for the help now I have just what I pretend, I see that You see in column I if exist any flight between the interval for example 4:00 - 5:00, and if yes you make this value plus the value one column K write?

My idea was only make the check on column I and if exist any flight between the interval than return the value of column K, but I will try to change this, if I can't do this I will ask you to help me again ok?

Thank you very much

regards,

Ismael