excel formula help.

[Bert2]

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.

 

[Denial]

I'm not 1000% sure I understand what you are trying to achieve, but conditional formatting can give you the sort of value-based highlighting you seem to be after. Use the "Formula is" test in the conditional formatting, and enter the formula
=OR(MOD(B2,1500)<200,MOD(B2,1500)>1300)
(where in this example B2 is the cell to be tested).

 

[IRstuff]

something like IF(MOD(ROW(a1),500)=0,a1,"")?

TTFN

FAQ731-376

 

[zappedagain]

How about a histogram (in the Data Analysis package) with the bin size set to 1500? It doesn't have the tolerance but it would give you a table with how many entries are in each bin.

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?

 

[Bert2]

the attached spreadsheet is an example of what im trying to get.

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.

 

[electricpete]

The attached comes close.

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.

 

[electricpete]

Attached is a version that resolves problems above by comparing cell above and below and only highlighting the cell that is closest to multiple of 1500. The following formula is used for conditional formatting of D5
[CODE D5 Conditional Format Formula]=AND(MIN(MOD($D5,1500), MOD(-1*$D5,1500))<MIN(MOD($D4,1500), MOD(-1*$D4,1500)),MIN(MOD($D5,1500), MOD(-1*$D5,1500))<MIN(MOD($D6,1500), MOD(-1*$D6,1500)),MIN(MOD($D5,1500), MOD(-1*$D5,1500))<150)[/code]

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[zelgar]

If you're wanting a conditional format to highlight everything that's a multiple of 1500 (e.g., 1500, 3000, 4500) then try the following code:
=IF(INT(D5/1500)-D5/1500=0,D5,"") (For Cell D5)

 

[zappedagain]

and with and ABS() and a '<' instead of an '=' you should get your tolerances too!

 

[electricpete]

int would only find things just above the multiple of 1500. Round would do a little better if you want to find results either above or below the multiple:
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.