Formula for Worksheet

  • Thread starter Thread starter cbscotty
  • Start date Start date
C

cbscotty

I need a formula that will compare two values in a
worksheet, and if the two values are greater than or less
than .4, it will average the number above with the number
after and place the average in the cell. See Example:

1.26
0.006
1.156


What I want is this. Cell 2 is a greater difference
than .4 when compared to cell 1. Since Cell 2 is a
greater difference, then I want to average cell 1 and 3
and produce the value for cell 2 in another column. If
cell #2 is not a greater difference than .4, then I just
want cell 2's value to appear in that column. I don't
want to change the values in cells 1-3 at all. A new
column can be started to produce the results and manage
the data from. Please respond to my email address above.

Thank you.
 
Thank you. That does work except I forgot one thing.
Once you correct the value that's out of tolerance, the
formula applied down a column will need to use the
corrected data as part of the calculation for the next
cell down. I'll try and copy part of my example below.
Can you help?

Thank you for your help with the formula. It did exactly
what I was looking for except for one thing. I attached
a copy of some of the data from my spreadsheet, so I can
show you what I'm really trying to accomplish. If you'll
look at column B, you'll see that each unit below is just
one more than the one above it. It is a reference point
for the next two numbers. For each cell in B, there is a
corresponding measurement in C and D. What I'm trying to
accomplish is that in both columns C and D, anytime a
reading varies by more than .4 from the measurement above
it, that's when we do the averaging of the cells above and
below it to actually replace the value that was out of
tolerance, and flag where the change took place. I'm
assuming that I'll need to reproduce the C and D columns
in E, F, G or so forth. I applied the formula you sent
me to column C and the result is in column F. If you'll
look at cell C23, this is a good example of the problem.
I only want to correct the value of C23 in this instance.
Not the value of C24. C23 is more than .4 difference from
C22. The calculation works correctly with the result in
F23. The problem then occurs in F24, because it uses the
value of F-22 for it's calculation. I need to put the
1.208 value of F23 in place of the value in C23 and then
continue from there. Does that make any sense. I'm
trying to correct any value in the C column that is .40
out of tolerance from the value above it, and then replace
it with the average of the one above and the one below.
Any ideas?


Coordinates Ft. Marker Left VDC Right VDC



N42 12.046 - W107 22.075 82429 0.979 1.403
line5 82430 1.164 1.224 1.164
6 82431 1.397 0.991 1.397
7 82432 1.03 1.165 1.03
8 82433 1.224 1.351 1.224
9 82434 1.388 1.021 1.388
10 82435 1.105 1.201 1.105
11 82436 1.292 1.385 1.292
12 82437 0.979 1.076 0.979
13 82438 1.159 1.265 1.159
14 82439 1.337 1.402 1.337
15 82440 0.979 1.098 0.979
16 82441 1.205 1.292 1.205
17 82442 1.382 0.96 1.382
18 82443 0.969 1.142 1.303
19 82444 1.224 1.31 1.224
20 82445 1.382 1.003 1.382
21 82446 0.974 1.192 1.321
22 82447 1.26 1.368 1.26
23 82448 0.006 1.068 1.208
24 82449 1.156 1.238 0.676
25 82450 1.346 1.375 1.346
26 82451 0.989 1.087
 
Back
Top