need spreadsheet help

Reply

  #1  
Old 03-20-18, 05:40 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,303
Received 61 Votes on 57 Posts
need spreadsheet help

What function would I use to see if three numeric cells all within 20 digits to one another. In other words if cell A1 has the number 15 and B1 has 30 and C1 has 21 then all three are not within 20 digit to each other even though A1 andC1 are within 20 digits. All three must be within 20 digits to each other.

In this case I'm using Libre Office Spreadsheet, but Excel or Google sheets are OK. I can't seem to find the function to use unless I make a very long if statement.
 
Sponsored Links
  #2  
Old 03-20-18, 06:05 PM
Member
Join Date: Apr 2015
Location: USA
Posts: 347
Received 3 Votes on 3 Posts
C1, C2, C3 are your cells.

Find 3 cells on your spreadsheet that you are not using and have them reflect the absolute difference between each other. You will hide these cells when done by making their color match the back ground so they can't be seen.

D1=ABS(C1-C2), D2=ABS(C1-C3), D3 =ABS(C2-C3)


Now the cell you want to tell you would have an if statement as such

=IF(AND(D1<20,D2<20,D3<20),1,2)

Where the result is 1 if they are within 20 and 2 if they aren't. Obviously you can change the results to whatever you want or do a conditional formatting to have it pop when you look at it.

If you want to do it all in one cell then
=IF(AND(ABS(C1-C2)<20,ABS(C1-C3)<20,ABS(C2-C3)<20),1,2)
which really isn't that long, not sure if there is a function that does the same thing.


Edit: Rereading what you wrote, this might not be what you want. Do you want the total of the difference to be <20? In your example all 3 cells are within 20???
 

Last edited by Tumble; 03-20-18 at 06:40 PM.
  #3  
Old 03-20-18, 06:35 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,303
Received 61 Votes on 57 Posts
Sorry, I added wrong. Assume A1is 15, B1is 30, but C1 is 40. Now A1 and C1 are no longer within 20 units if each other (25 units difference
). I need all three cells to be within 20 units between each other. I think what you wrote is what I want. But it's getting late and I'm having trouble thinking straight.

I'll dissect it tomorrow. But anything you want to add would be appreciated.
 
  #4  
Old 03-21-18, 05:14 AM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,303
Received 61 Votes on 57 Posts
Your formulas almost do what I want. The hidden formulas (ref ABS function) does what it's suppose to do. That's great and I may be able to just use that part of it.

The IF statement is not what I want. The values don't need to be less than 20 but each cell must be within 20 units of each other no matter what the actual values are.

So what would be nice is if I can get the results from the ABS function to show up in one cell indicating that the three value do or don't fall within 20 digits of each other with a pass or fail condition. I don't need to know what cells are not within the 20 digit limit, only that they are or are not.

EDIT...I think the MAX statement might be what I need. It will tell me in one cell if any of my values from the ABS statement exceeds 20. Not quite the elegance I'm looking for but it may work.

What I'm trying to do is record the values generated by my 3D printer heated bed calibration. Each calibration point must be within 20 units of each other.
 

Last edited by Norm201; 03-21-18 at 05:32 AM.
  #5  
Old 03-21-18, 06:11 AM
Member
Join Date: Nov 2012
Location: USA
Posts: 1,225
Received 23 Votes on 19 Posts
MIN and MAX functions should work, if you have data in Cells A1, B1, C1

=(MAX(A1:C1) - MIN(A1:C1))

should give you the range of numbers.

Then there are "IF" functions that you could use to act if the difference in the range was more than 20
 
  #6  
Old 03-21-18, 06:10 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,303
Received 61 Votes on 57 Posts
Hal,

Thank you, this is exactly what I want. Should've thought of it myself.
This works out very well. With your MAX/MIN statement I can record the difference between all three and with Tumbles ABS statement I can also record the difference between each group of cells.

Thanks guys.
 
Reply

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread
Display Modes
 
Ask a Question
Question Title:
Description:
Your question will be posted in: