## IF/AND/OR... creating a 'within' perameter check

379 Views 11 Replies Latest reply: Aug 14, 2012 3:19 PM by KPRTX
Calculating status...
Currently Being Moderated
Aug 10, 2012 6:03 PM

THis is from a previous post, thanks to the local genuises for helping me out with this.

I'm having a similar problem that I can't figure out.  I'm trying to check a value in column K(baby blue ) against column J (purple).  It should check to see if K is within .8 of J, if TRUE then just return K (which is in this case (H8-G8)*24) if FALSE or outside, then return CHECK.  What have I done wrong?  Examples usually help out, as I'm not too good with the jargon.  Thanks!

I actually figured this one out on my own but after reading barry's response, he also nailed it, and he didn't have the whole picture.  I will address the 2 issues

1.) The values are compatible (maybe because I'm using numbers 08) (H8-G8) returns a .XX as part of a day (1 being 24 hours).

2.)  I started mixed up the logic from my original idea and should have used OR instead of AND, as berry pointed out.  I added another photo below that shows the new formula working, and the time zone designator.

If you want to see the whole breakdown here goes:

Orange(G) is the start time.

Dark Blue(H) is the end time.

Baby Blue(K) is the duration (H8-G8)*24 converted to hours with 1 decimal

Purple (J) is the value Baby Blue(K) is being compared too.

If the value of K is greater than approximately .8 from the value of J then it represents a time zone error.

As of yet I haven't thought of way to auto correct the time zone error, but this will bring it to my attention so I can manually check it.  If it only erred in one direction in a standard numerical value I could add it, but it varries -1 to +2.  Farther left it has a city designation (not in the photo) that I can use to figure out the error.  Any easy idea's on making an auto correct function would be cool too, just remember I'm not so good at this so take it easy on me!

Thanks so much for all your help friends!  This has been (and will continue to be) a fun learning experience.

KP

• Level 7 (27,365 points)
Currently Being Moderated
Aug 11, 2012 8:08 AM (in response to KPRTX)

Sir KP,

You could use this form for the test to see if two values are within a fixed difference in either direction:

=IF(ABS(G-H))>0.8, "Check", (H-G)*24)

Regards,

Jerry

• Level 7 (27,365 points)

Correction...

An extra right parenthesis slipped in there. Should be:

=IF(ABS(G-H)>0.8, "Check", (H-G)*24)

Regards,

Jerry

• Level 7 (27,365 points)
Currently Being Moderated
Aug 11, 2012 10:44 AM (in response to KPRTX)

Thanks for the feedback. The ABS (absolute) function is a simple impementation of the math operator that strips the sign from the value. All results are positive.

Jerry

• Level 7 (27,365 points)
Currently Being Moderated
Aug 11, 2012 11:40 AM (in response to KPRTX)

KP,

You can use Conditional Format to do that. First you must rework your expression to add a flag to indicate the condition you want to act on. One pretty good way is to add a space character to the beginning or end of a string, and detect that in conditional format.

Here's an example:

=IF(ABS(G-H)>0.8, " "&(H-G)*24, (H-G)*24)

In Conditional Format, test for "Text Starts With" and give it a "space" in the comparison box.

I can't check that for you on my version because I'll get a value type mismatch error.

Jerry

• Level 7 (28,790 points)

I can confirm that Jerry's formula works in Numbers '08.

Moving on to the second part of your question:

"As of yet I haven't thought of way to auto correct the time zone error, but this will bring it to my attention so I can manually check it.  If it only erred in one direction in a standard numerical value I could add it, but it varries -1 to +2.  Farther left it has a city designation (not in the photo) that I can use to figure out the error.  Any easy idea's on making an auto correct function would be cool too, just remember I'm not so good at this so take it easy on me! "

Auto-correcting the times to a common time zone might provide an alternate route to your end result.

Numbers will need the time zone information to make the necessary correction. I'm working on a couple of assumptions, based on your descriptions above, what time zones these are and how your table is set up.

1. All times in column G are in the same time zone (for my example, the Central zone)

2 Times in column H may be in any of four time zones, Eastern, Central, Mountain or Pacific.

3 You want to convert the times in H to the same 'instant' in the Central zone.

'Local' times are in column H, those times converted to CST (or CDT) are in column I. Formula shown above the table, and in a version without the use of column headers as cell references, below:

=H+(LOOKUP(D,Table 2::A,Table 2::B)/24)

Table 1 is your main table. Table 2 is the two column table to the right, listing the time offset needed to for each time zone.

A larger lookup table could be used to lookup the time zone for each city, then use that result to lookup the offset for that zone.

Regards,

Barry

• Level 7 (27,365 points)
Currently Being Moderated
Aug 14, 2012 4:17 AM (in response to KPRTX)

Kelly,

Your problem when going from PM to AM is that the day has rolled over, or should have. In iWork, all times include a date as well. This can cause some problems in some cases and can solve a problem in others. If you enter just a Time, the current Date is appended, behind the scenes. Had you entered the entire Date and Time for your data point, you wouldn't have had the negative result, but that's rather inconvenient.

A handy work-around is to use this expression for elapsed time or time difference:

=time2 - time1 + if(time1 > time2, 1, 0)

The result of that expression is that 1 day is added (24 hours) if the day should have rolled over and didn't.

Regards,

Jerry

#### More Like This

• Retrieving data ...