Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Formula to Filter 3 columns & enter data from 3rd

Have a spreadsheet called Data which has columns with Date, Time & Data. The second spreadsheet called Summary has columns for Date & 4 time periods where the Data is entered.

Am currently doing this using the formula SUMIFS(Data::C,Data::A,A3,Data::B,">10:30",Data::B,"<=15:30").

This works ok if there are just one set of figures per time period but if there are more then it just adds them together.

Is it possible to use a formula that would enter just one set of figures ideally the lowest from a set? ie. there are 3 for the period 20:01 to 24:00 - 8.7, 6.2, 5.7 show 5.7.

Am using Numbers version 4.3.1 (5249) on a iMac (Retina 5K, 27-inch, Late 2014)

Thanks in anticipation

iMac, OS X Yosemite (10.10.3), Numbers (3.5.3)

Posted on

Question marked as Solved

Alan,

I looking at your document, the main problem is that the dates entered in the table "Data" were formatted as:

mm/dd/yy

where as the dates in the table "Summary" were enter, for some reason, as:

dd/mm/yy

In addition I seem to have a problem relaying my correct formulas.

The formula for F2 should be:

=IF(E2≠E1, 0, F1+1)

I know how it go messed up too!

notice that the formula has the not equal operator as an equal sign with a slash. I tried to correct this by pasting directly from Numbers to this forum, then I tried, unsuccessfully, to change the

≠ to <>

Final formula is:

=IF(E2<>E1, 0, F1+1)

Also... in the table "Summary", the formula for B3 should be:

=SUMIFS(Data::\$G, Data::\$A, \$A3, Data::\$E, B\$1)

copy and paste this formula to the range:

B3 thru the end of column E

the format of the dates in column A of table Data and column A of the summary table MUST be the same. I corrected this. I do not know why they were different formats.

I will email the corrected document back to you.

Posted on

maybe try using the average of the entries?

=sumifs(Data::C, Data::A, A3, Data::B, ">10:30", Data::B, "<=15:30")/countifs(A3, Data::B, ">10:30", Data::B, "<=15:30")

There’s more to the conversation

maybe try using the average of the entries?

=sumifs(Data::C, Data::A, A3, Data::B, ">10:30", Data::B, "<=15:30")/countifs(A3, Data::B, ">10:30", Data::B, "<=15:30")

Dec 12, 2017 8:52 AM

Yes thanks for that Wayne, would prefer to use one of the actual numbers if possible

Dec 12, 2017 8:55 AM

Hope this helps

 Date Time Data 01/10/17 07:56 5.8 01/10/17 12:12 5.7 01/10/17 13:01 8.5 01/10/17 17:23 7.3 01/10/17 22:02 6.7 02/10/17 07:32 7.2 02/10/17 12:09 6.8 02/10/17 17:01 7.4 02/10/17 21:58 8.7 02/10/17 22:31 6.2 02/10/17 23:58 5.7 03/10/17 07:37 7.6 03/10/17 12:18 7.1 03/10/17 17:16 5.9 03/10/17 22:11 9.1 04/10/17 07:42 4.3 04/10/17 12:30 5.6 04/10/17 17:05 9.0 04/10/17 22:15 8.4 05/10/17 07:29 4.7 05/10/17 12:19 7.0 05/10/17 16:55 6.1 05/10/17 20:13 3.8

Dec 12, 2017 10:03 AM

Having Excel's =MINIFS() would make this a lot easier!

But are you looking for results like this?

If so, here is how I did it.

First, I added a column to the data table showing the time period for each entry.

That could be done manually, or automated with a lookup table something like this (I had to kludge a little for midnight):

Then I added two columns to get the first row number for that date and the last row number for that date. That is used later in a special form of the OFFSET() function.

First row for that date:

Last row for that date:

Then I added more columns with labels like this:

The formula in G2, filled right to J2, then down, is:

=IF(\$D2=G\$1,\$C2,"")

The formula in K2, filled right to N2, then down, is:

=MIN(OFFSET(G\$1,\$E2−1,0,\$F2−\$E2+1,1))

The formula in O2, filled down is:

=COUNTIF(A\$1:A2,A2)

Then I hid columns E:J and filtered on column O:

SG

Dec 12, 2017 12:08 PM

Here is a way that may do what you want:

Create one table to set the time periods (since they are not regular):

the first row should be formatted as a header row.

enter the value in column A and C as shown.

select cell B2 and type (or copy and paste from here) the formula:

=TIMEVALUE(A2)

shorthand for this is:

B2=TIMEVALUE(A2)

to fill down , select cell B2, copy

now select cells C2 thru the end of column C, paste

Now add some columns to the table "Data":

D2=A2&"-"&VLOOKUP(TIMEVALUE(B2), Time Periods::B:C, 2,TRUE)

E2=VLOOKUP(TIMEVALUE(B2), Time Periods::B:C, 2,TRUE)

F2=IF(E2<E\$1, 0, F1+1)

G2=IF(OR(F3<F2, AND(F2=0, F3=0)),MIN(OFFSET(C2, −F2, 0, 1+F2, 1)), "")

fill these down by

select cells D2 thru G2, copy

select cells D2 thru the end of column G, paste

Now complete the summary table:

set up the first two rows as show (columns B thru E)

B3=SUMIFS(Data::\$G, Data::\$A, \$A3, Data::\$E, B1)

select cell B3, copy

select cells B3 thru the end of column E, paste

Dec 12, 2017 1:23 PM

Thanks Wayne for your rapid response this seems to be what I require but am having trouble with the formula for Data cell F2. Not sure what I am doing wrong but when I fill down the column all answers are 0.

Dec 13, 2017 8:26 AM

screenshots and formulas always help.

Post the formula in the cell now, and a screenshot of the the cell with the formula selected

Dec 13, 2017 8:58 AM

alanavey wrote:

Not sure what I am doing wrong but when I fill down the column all answers are 0.

Have you had time to try the solution I suggested above? I tested it before posting. No zeros. Friendly-looking output (not numbers representing time periods but actual time period labels).

SG

Dec 13, 2017 1:53 PM

Thanks for your response, haven’t had opportunity to look at your solution yet hoping to look tomorrow

Dec 13, 2017 2:23 PM

Still having issues with some of formulas as attached screenshot.

Have copied and pasted formulas used but having issues with contents columns F & G

D2= A2&"-"&VLOOKUP(TIMEVALUE(B2), Time Periods::B:C, 2,TRUE)

E2=VLOOKUP(TIMEVALUE(B2), Time Periods::B:C, 2,TRUE)

F2=IIF(E2<E1, 0, F1+1)

G2=IF(OR(F3<F2, AND(F2=0, F3=0)),MIN(OFFSET(C2, −F2, 0, 1+F2, 1)), “")

Dec 14, 2017 1:15 AM

Why not give the solution I suggested above a try? One of its virtues is shorter formulas. It should take about 5 minutes to set up.

SG

Dec 14, 2017 1:46 AM

User profile for user: alanavey

Question: Formula to Filter 3 columns & enter data from 3rd