Formula Trouble

jcurran70

Board Regular
Joined
Oct 23, 2002
Messages
82
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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

Board Regular
Joined
Oct 23, 2002
Messages
82
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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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

Board Regular
Joined
Oct 23, 2002
Messages
82

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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

Board Regular
Joined
Oct 23, 2002
Messages
82

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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.)
 

Forum statistics

Threads
1,144,743
Messages
5,726,022
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top