Cigar Asylum Cigar Forum

Cigar Asylum Cigar Forum (http://www.cigarasylum.com/vb/index.php)
-   Networking (http://www.cigarasylum.com/vb/forumdisplay.php?f=123)
-   -   Any Excel gurus around? (http://www.cigarasylum.com/vb/showthread.php?t=71955)

Steve 08-29-2016 10:47 AM

Any Excel gurus around?
 
Things are slow around the office today, so I am trying to work up a spreadsheet to help with my daughters archery practice.

Basically, she will be judging distances this year, so I have a spreadsheet with the actual yardage and the yardage she has judged (or guessed). What I want is for the spread sheet to tell me yes if she has judged within 1.5 yards (either direction) of the actual distance I have ranged and no if she is off by more than 1.5 yards either direction.

Can I get there from here?

Thanks in advance,

Steve

pnoon 08-29-2016 10:59 AM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by Steve (Post 2099765)
Things are slow around the office today, so I am trying to work up a spreadsheet to help with my daughters archery practice.

Basically, she will be judging distances this year, so I have a spreadsheet with the actual yardage and the yardage she has judged (or guessed). What I want is for the spread sheet to tell me yes if she has judged within 1.5 yards (either direction) of the actual distance I have ranged and no if she is off by more than 1.5 yards either direction.

Can I get there from here?

Thanks in advance,

Steve

Email me a sample or portion of the spreadsheet. I should be able to work something up for you.
pnoon56 at cigarasylum dot com

markem 08-29-2016 11:04 AM

Re: Any Excel gurus around?
 
It sounds like you want to know if the difference between 2 cell values is less than some limit. If it is, print "YES" else print "NO".

Is this correct?

pnoon 08-29-2016 11:07 AM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by markem (Post 2099767)
It sounds like you want to know if the difference between 2 cell values is less than some limit. If it is, print "YES" else print "NO".

Is this correct?

That's how I see it, too.
I was thinking to color code the NO as red and YES within 1.5 yards as green and within 0.75 yards as dark green.

stearns 08-29-2016 11:08 AM

Re: Any Excel gurus around?
 
Happy to help as well, I spend most of most of my days in excel. I think what Mark is eluding to is all you need, something like a =IF(x-y<1.5,YES,NO) where x is the cell with the actual distance and y is the cell with the estimated distance. You can do basic formulas like this in a gsheet if you want to be able to use it on the run through google drive. Let me know if I can be of assistance :tu

markem 08-29-2016 11:09 AM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by stearns (Post 2099769)
Happy to help as well, I spend most of most of my days in excel. I think what Mark is eluding to is all you need, something like a =IF(x-y<1.5,YES,NO) where x is the cell with the actual distance and y is the cell with the estimated distance. You can do basic formulas like this in a gsheet if you want to be able to use it on the run through google drive. Let me know if I can be of assistance :tu

This line works for me:

=IF((ABS(B1-A1)<1.5), "TRUE", "FALSE")

the column information is spreadsheet specific, but you get the idea.

stearns 08-29-2016 11:12 AM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by markem (Post 2099770)
This line works for me:

=IF((ABS(B1-A1)<1.5), "TRUE", "FALSE")

the column information is spreadsheet specific, but you get the idea.

Forgot about ABS, that will make things smoother :tu

pnoon 08-29-2016 11:20 AM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by markem (Post 2099770)
This line works for me:

=IF((ABS(B1-A1)<1.5), "TRUE", "FALSE")

the column information is spreadsheet specific, but you get the idea.

I'm only guessing here but I would assum the boundary condition of 1.5 would be a positive result. If so, reversing the sign and results will do the trick

=IF((ABS(B1-A1)>1.5), "FALSE", "TRUE")

The literals could also be changed to PASS/FAIL, YES/NO, etc.

Steve 08-29-2016 11:32 AM

Re: Any Excel gurus around?
 
Yes, that's pretty much what I was looking for. For now I am ok with her being within 1.5 yards either direction, but as we go on, I want to be able to adjust that. I have a column set up to indicate if she is + or Minus on the distance.

I was able to get a yes/no if b=a, but couldn't quite figure out how to do in a range.

markem 08-29-2016 11:35 AM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by Steve (Post 2099774)
Yes, that's pretty much what I was looking for. For now I am ok with her being within 1.5 yards either direction, but as we go on, I want to be able to adjust that. I have a column set up to indicate if she is + or Minus on the distance.

Here is a good source for more info on if() constructs:

https://exceljet.net/formula/nested-if-function-example

Steve 08-29-2016 11:42 AM

Re: Any Excel gurus around?
 
http://oldchurchbbq.com/sharedpictur...el/Ranging.png

benedic08 08-29-2016 11:57 AM

Re: Any Excel gurus around?
 
You can set aside a cell and put in 1.5 in that cell. For example we use cell X1 and have 1.5 in there. You can edit Peter's formula =IF((ABS(B1-A1)>X1), "FALSE", "TRUE"). To make adjustments, you can quickly change X1 to 1 or .5 and all the formulas pointing to it will adjust accordingly.

pnoon 08-29-2016 11:58 AM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by Steve (Post 2099776)

:cl
Quote:

Originally Posted by benedic08 (Post 2099777)
You can set aside a cell and put in 1.5 in that cell. For example we use cell X1 and have 1.5 in there. You can edit Peter's formula =IF((ABS(B1-A1)>X1), "FALSE", "TRUE"). To make adjustments, you can quickly change X1 to 1 or .5 and all the formulas pointing to it will adjust accordingly.

:tu

Wharf Rat 08-29-2016 05:54 PM

Re: Any Excel gurus around?
 
He probably wants to have the formula in multiple rows. So X1 needs to be an absolute address: $X$1

benedic08 08-29-2016 07:57 PM

Re: Any Excel gurus around?
 
Quote:

Originally Posted by Wharf Rat (Post 2099842)
He probably wants to have the formula in multiple rows. So X1 needs to be an absolute address: $X$1

Ah yes forgot a tiny but super important detail hahahah :D

stearns 08-30-2016 07:24 AM

Re: Any Excel gurus around?
 
F4! F4!

markem 08-30-2016 09:34 AM

Re: Any Excel gurus around?
 
2 Attachment(s)
I decided to put some ideas in one post, so this is a duplicate at first.

Here is the formula, based on your example. This example uses a column value for the limit. Note that you can copy this formula to all the subsequent rows of the column and it will automatically use the right cells. This is an Excel thing, not something I did.

http://www.cigarasylum.com/vb/attach...1&d=1472570949

I also noticed the "target" column and decided that selecting from a drop down list that you can maintain in a column, or even on another page, would be nice. Use the "data validation" selection on the "data" tab to get to this.

http://www.cigarasylum.com/vb/attach...1&d=1472571128

mhailey 08-30-2016 09:36 AM

Re: Any Excel gurus around?
 
here is the formula for the average of Yes responses:

=COUNTIF(E2:E5,"Yes")/COUNTA(E2:E5)

Steve 08-30-2016 10:51 AM

Re: Any Excel gurus around?
 
Thanks guys! I did make a pull down for the various targets, it just didn't show up on the screenshot.


All times are GMT -6. The time now is 01:57 AM.

Powered by vBulletin® Version 3.7.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.