Stuck on a Formula

[cranen]

This is my data (5 columns). I have a custom format on it (##+##.00). I am
trying to get a formula to add values at 50 feet before the first value in
the row, 50 feet after each value in the row until it reaches the next value
then repeat, and 50 feet after the ending value in the row (the new rows of
data are being placed out beside the old data). I have tried for two days
everything I know to do, and I can't get it to work. Below the data are the
last formulas that I have written (One is for the first cell, and the other
is for the following cells). I could really use some help. Thanks.

20+83.25 23+83.25 24+48.23 25+11.02 28+11.02
43+49.91 45+49.91 46+08.68 46+64.08 48+64.08
50+58.42 52+08.42 52+12.44 52+15.80 53+65.00
68+36.40 71+36.40 72+29.87 73+20.29 76+20.29
86+35.30 88+85.30 89+58.95 90+15.30 92+65.30
106+88.10 109+88.10 110+57.15 111+17.27 114+17.27
121+53.22 124+03.22 126+39.39 128+33.55 130+83.55
144+60.21 146+60.21 146+98.18 147+32.09 149+32.09
158+44.44 161+44.44 161+80.12 162+09.92 165+09.92
174+33.17 176+83.17 177+70.58 178+47.46 180+97.46
199+11.35 201+11.35 201+37.01 201+59.47 209+65.54
203+89.04 205+89.04 206+85.26 207+65.54 209+65.54
211+82.95 215+82.95 217+68.98 218+98.78 222+98.78
238+54.52 242+04.52 242+63.69 243+15.02 246+65.02

=ROUNDDOWN(D3,-2)

=IF(AND(($D$3-R3)<50,($D$3-R3)>0), $D$3, IF(AND(($E$3-R3)<50, ($E$3-R3)>0),
$E$3, IF(AND(($G$3-R3)<50, ($G$3-R3)), $G$3, IF(AND(($I$3-R3)<50, ($I$3-R3)),
$I$3, IF(AND(($J$3-R3)<50, ($J$3-R3)), $J$3, MROUND(R3,(R3+50)))))))

 

[Daryl S]

Cranen -

You are not giving us enough information. The custom format does not change
the data values, just how they are displayed. Does your custom formatting
have anything to do with the '50 feet' criteria? Remember the first cell is
really 2083.25 no matter how it is formatted.

I see your ROUNDDOWN(D3,-2) near the bottom. Are you maybe trying to round
the data you show above to only contain the values to the left of the "+"
sign? if so, then use ROUNDDOWN(D3,-2)/100. Does this maybe equate to the
feet? If you do that for all cells, your data will look more like this:

20 23 24 25 28
43 45 46 46 48
50 52 52 52 53
68 71 72 73 76
86 88 89 90 92
106 109 110 111 114
121 124 126 128 130
144 146 146 147 149
158 161 161 162 165
174 176 177 178 180
199 201 201 201 209
203 205 206 207 209
211 215 217 218 222
238 242 242 243 246

Please post a little more information, like what the results should be. Can
you walk us through what the values should be for the first row, and how you
figured that out? Are you expecting 7 new columns (one for values at 50 feet
before the furst value, one for 50 feet after each value in the row (whatever
that means), and one for 50 feet after the ending value in the row)?

Post more details so we can help more...

 

[cranen]

Daryl S-

I apologize. Its hard for me at times to fully explain whats going on in my
spreadsheets. I did figure out my problem though. Here is what I came up
with:


=IF(AND(($D$3-R3)<50,($D$3-R3)>0),$D$3,IF(AND(R3=$D$3,(R3+50)<$E$3),CEILING(R3,50),IF(AND(($E$3-R3)<50,($E$3-R3)>0),$E$3,IF(AND(R3=$E$3,
(R3+50)<$G$3),
CEILING(R3,50),IF(AND(($G$3-R3)<50,($G$3-R3)>0),$G$3,IF(AND(R3=$G$3,(R3+50)<$I$3),CEILING(R3,50),IF(AND(($I$3-R3)<50,($I$3-R3)>0),$I$3,IF(AND(R3=$I$3,
(R3+50)<$J$3),CEILING(R3,50),IF(AND(($J$3-R3)<50,($J$3-R3)>0),$J$3,IF(R3=$J$3,CEILING(R3,50),MROUND(R3,(R3+50))))))))))))

Thanks