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

Question:

Question: 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

User uploaded file

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

Posted on

Reply
Question marked as Solved
Answer:
Answer:

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

There’s more to the conversation

Read all replies

Dec 12, 2017 10:03 AM in response to Wayne Contello In response to Wayne Contello

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

Reply Helpful

Dec 12, 2017 12:08 PM in response to alanavey In response to alanavey

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


But are you looking for results like this?

User uploaded file



If so, here is how I did it.


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


User uploaded file


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


User uploaded file


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:

User uploaded file


Last row for that date:

User uploaded file


Then I added more columns with labels like this:


User uploaded file


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:


User uploaded file


SG

Dec 12, 2017 12:08 PM

Reply Helpful

Dec 12, 2017 1:23 PM in response to alanavey In response to alanavey

Here is a way that may do what you want:



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

User uploaded file


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":

User uploaded file


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:

User uploaded file


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

Reply Helpful

Dec 13, 2017 1:53 PM in response to alanavey In response to alanavey

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

Reply Helpful

Dec 14, 2017 1:15 AM in response to Wayne Contello In response to Wayne Contello

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)), “")

User uploaded file

User uploaded fileUser uploaded file

User uploaded file

User uploaded file

User uploaded file

Dec 14, 2017 1:15 AM

Reply Helpful
User profile for user: alanavey

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