excel formula help.
excel formula help.
(OP)
Hi,
I'm wondering if someone could assist me in writing a formula for excel.
im a bit rusty on it and haven't used it in depth for around 2years.
what i require is a formula to flag up values every 1500units with a tolerance of + -- 200.
for example i have a long list of accumulating values from 0 to 15000 in which i require every 1500 with a tolerance of + -- 200 to be shown, in a way like conditional formatting does.
can anyone point me in the right direction?
thanks.
I'm wondering if someone could assist me in writing a formula for excel.
im a bit rusty on it and haven't used it in depth for around 2years.
what i require is a formula to flag up values every 1500units with a tolerance of + -- 200.
for example i have a long list of accumulating values from 0 to 15000 in which i require every 1500 with a tolerance of + -- 200 to be shown, in a way like conditional formatting does.
can anyone point me in the right direction?
thanks.





RE: excel formula help.
=OR(MOD(B2,1500)<200,MOD(B2,1500)>1300)
(where in this example B2 is the cell to be tested).
RE: excel formula help.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: excel formula help.
Your tolerance requirement is a bit confusing the way you stated it (flag up?). Can you give an example table of your inputs and your desired outputs?
RE: excel formula help.
it shows an accumulating value (random) on the left, the columb on the right is a 'sum' formula addin the values in the left columb with a conditional format to show values in red between 1450 - 1500.
however i need this for every 1500, rolling on.
hope this helps.
RE: excel formula help.
Conditional formatting for cell D20 is:
=OR(MOD(D20,1500)<200, MOD(-1*D20,1500)<200)
Note the first argument of or finds items within 200 above a multiple of 1500 and the 2nd finds items within 200 below a multiple of 1500.
Two problems:
1 - It flags items within 200 of 0 which I don't think you want.
2 - If two or more items fall within the range they are both highlighted... I'm not sure if that is desired functionality or not. If not, would you like it to only flag the first (lowest value) entry that falls within the range 1500+/-200... or to flag the one closest to a multiple of 1500? To accomplish these we would have to look at neighboring values and apply some logic. Also have to decide how many neighboring values to look at.. requires knowledge of the minimum possible value of entries in column C.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: excel formula help.
CODE --> D5 Conditional Format Formula
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: excel formula help.
=IF(INT(D5/1500)-D5/1500=0,D5,"") (For Cell D5)
RE: excel formula help.
RE: excel formula help.
ABS(ROUND(D5/1500,0)*1500-D5)<150
Could check cells above/below similar to what was posted above.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.