Formula Trouble

[jcurran70]

I am having trouble creating a formula that works correctly. Say I have 5 columns set up for day 1 through day 5. In the row 1 have currency rates for all but day 4.

I am trying to figure out a formula that will look at the data and let me know if any of my data points is above a tolerance level. But I need to exclude any day I do not have a value for like day 4. Any suggestions on how I may create this?

 

[Asala42]

I am having trouble creating a formula that works correctly. Say I have 5 columns set up for day 1 through day 5. In the row 1 have currency rates for all but day 4.

I am trying to figure out a formula that will look at the data and let me know if any of my data points is above a tolerance level. But I need to exclude any day I do not have a value for like day 4. Any suggestions on how I may create this?

How are you determining tolerance levels? Can you give us an example?

 

[jcurran70]

tolerance level would very based upon the different currency values. I am thinking that may have to be a harcoded value for each formula given the varying size of swings based on size of the currency value.

Example
GBP
Day1 Day 2 Day 3 Day 4 Day 5
.55 .54 .52 .55
Exceptable tolerance may be a difference of .05

 

[Asala42]

Example
GBP
Day1 Day 2 Day 3 Day 4 Day 5
.55 .54 .52 .55
Exceptable tolerance may be a difference of .05

Is the example above, are you comparing the minimum to maximum (excluding 0's) to determine if you're within tolerance? or is this a day-to-day comparison.

For example is the following Day1-Day5 set considered outside or inside an acceptable tolerance of .05:

.55, .57, .59, .61, .63

 

[jcurran70]

This would be a day to day comparison. The data series you had would be in tolerance. As long as the difference between two days was within the .05 (in the given example) it would be in tolerance. If one of the days had no value I need to not include that, so that it would not kick out an out of tolerance.

 

[Asala42]

ok. In your example above (with missing data in day 4), do you need to compare day3 to day5? I think I'm starting to see your pain.

Also, are you actually looking at more than 5 days? What about more than 1 missing data points. For example is this possible:

0.5, (blank), (blank), (blank), .61
This message was edited by Asala42 on 2002-11-05 10:24

 

[jcurran70]

That is exactly what I need to do. I even thought about comparing a single day to the average for the period. Basically I am just trying to see if there are any big changes, indicating bad data or a material change in currency rate. I have some flexibility as exactly how I go about it.

 

[Asala42]

I think the easiest (but clumsiest) approach would be to use a couple extra rows. For example (I believe a somewhat hairy array formula or UDF may be your only alternatives):

To remove blanks:
Row 2 (directly under row 1): =if(A1=0,B1,A1)

Then to get your delta:
Row 3 (under row 2 offset one cell to the right: =if(Abs(A2-B2)>.05,1,0) copied to the right

Then you can use another if on row 3 to determine whether or not you're in tolerance:

=if(Sum(A3:E3)>0,"Tolerance Violation","Points within Tolerance")

HTH (maybe someone can post a cleaner solution.)

 

[jcurran70]

I will try that. Thanks for you help.