×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

excel formula help.

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.  

RE: excel formula help.

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).
 

RE: excel formula help.

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?   

RE: excel formula help.

(OP)
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.  

RE: excel formula help.

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.

RE: excel formula help.

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)

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

RE: excel formula help.

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)

RE: excel formula help.

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

RE: excel formula help.

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources