Cigar Asylum Cigar Forum  

Go Back   Cigar Asylum Cigar Forum > Non Cigar Specialty Forums > Misc > Networking

Reply
 
Thread Tools Display Modes
Old 08-29-2016, 11:47 AM   #1
Steve
Screen Name Steve
 
Steve's Avatar
 
Join Date: Oct 2008
First Name: Steve
Location: 17 R 435648 3354895
Posts: 6,807
Trading: (6)
HdM
Steve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud of
Default 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
__________________
...So don't sit upon the shoreline and say you're satisfied,
Choose to chance the rapids and dare to dance that tide
Steve is offline   Reply With Quote
Old 08-29-2016, 11:59 AM   #2
pnoon
YNWA
 
pnoon's Avatar
12
 
Join Date: Oct 2008
First Name: Peter
Location: San Diego
Posts: 29,811
Trading: (20)
RA
pnoon has disabled reputation
Default Re: Any Excel gurus around?

Quote:
Originally Posted by Steve View Post
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
__________________
I'm going recreational.

Be more concerned with your character than your reputation, because your character is what you really are, while your reputation is merely what others think you are.
-John Wooden

2 of 3 Requirements for use of the CA Rolodex: 100 posts/ 60 day membership/participation in trade (trader rating). New members can be added at any time.
pnoon is offline   Reply With Quote
Old 08-29-2016, 12:04 PM   #3
markem
Mostly Harmless
 
markem's Avatar
11
 
Join Date: Oct 2008
First Name: Mark
Location: Casa TJ
Posts: 16,002
Trading: (47)
HUpmann
markem has disabled reputation
Default 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?
markem is offline   Reply With Quote
Old 08-29-2016, 12:07 PM   #4
pnoon
YNWA
 
pnoon's Avatar
12
 
Join Date: Oct 2008
First Name: Peter
Location: San Diego
Posts: 29,811
Trading: (20)
RA
pnoon has disabled reputation
Default Re: Any Excel gurus around?

Quote:
Originally Posted by markem View Post
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.
__________________
I'm going recreational.

Be more concerned with your character than your reputation, because your character is what you really are, while your reputation is merely what others think you are.
-John Wooden

2 of 3 Requirements for use of the CA Rolodex: 100 posts/ 60 day membership/participation in trade (trader rating). New members can be added at any time.
pnoon is offline   Reply With Quote
Old 08-29-2016, 12:08 PM   #5
stearns
Dogbert Consultant
 
stearns's Avatar
13
 
Join Date: Oct 2008
First Name: Ben
Location: RiNo
Posts: 3,851
Trading: (50)
RA
stearns is just really nicestearns is just really nicestearns is just really nicestearns is just really nicestearns is just really nice
Default 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
__________________
"I have no idea what I'm doing, but I know I'm doing it really really well" - Andy Dwyer
stearns is offline   Reply With Quote
Old 08-29-2016, 12:09 PM   #6
markem
Mostly Harmless
 
markem's Avatar
11
 
Join Date: Oct 2008
First Name: Mark
Location: Casa TJ
Posts: 16,002
Trading: (47)
HUpmann
markem has disabled reputation
Default Re: Any Excel gurus around?

Quote:
Originally Posted by stearns View Post
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
This line works for me:

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

the column information is spreadsheet specific, but you get the idea.
markem is offline   Reply With Quote
Old 08-29-2016, 12:12 PM   #7
stearns
Dogbert Consultant
 
stearns's Avatar
13
 
Join Date: Oct 2008
First Name: Ben
Location: RiNo
Posts: 3,851
Trading: (50)
RA
stearns is just really nicestearns is just really nicestearns is just really nicestearns is just really nicestearns is just really nice
Default Re: Any Excel gurus around?

Quote:
Originally Posted by markem View Post
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
__________________
"I have no idea what I'm doing, but I know I'm doing it really really well" - Andy Dwyer
stearns is offline   Reply With Quote
Old 08-29-2016, 12:20 PM   #8
pnoon
YNWA
 
pnoon's Avatar
12
 
Join Date: Oct 2008
First Name: Peter
Location: San Diego
Posts: 29,811
Trading: (20)
RA
pnoon has disabled reputation
Default Re: Any Excel gurus around?

Quote:
Originally Posted by markem View Post
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.
__________________
I'm going recreational.

Be more concerned with your character than your reputation, because your character is what you really are, while your reputation is merely what others think you are.
-John Wooden

2 of 3 Requirements for use of the CA Rolodex: 100 posts/ 60 day membership/participation in trade (trader rating). New members can be added at any time.
pnoon is offline   Reply With Quote
Old 08-29-2016, 12:32 PM   #9
Steve
Screen Name Steve
 
Steve's Avatar
 
Join Date: Oct 2008
First Name: Steve
Location: 17 R 435648 3354895
Posts: 6,807
Trading: (6)
HdM
Steve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud of
Default 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.
__________________
...So don't sit upon the shoreline and say you're satisfied,
Choose to chance the rapids and dare to dance that tide
Steve is offline   Reply With Quote
Old 08-29-2016, 12:35 PM   #10
markem
Mostly Harmless
 
markem's Avatar
11
 
Join Date: Oct 2008
First Name: Mark
Location: Casa TJ
Posts: 16,002
Trading: (47)
HUpmann
markem has disabled reputation
Default Re: Any Excel gurus around?

Quote:
Originally Posted by Steve View Post
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
markem is offline   Reply With Quote
Old 08-29-2016, 12:42 PM   #11
Steve
Screen Name Steve
 
Steve's Avatar
 
Join Date: Oct 2008
First Name: Steve
Location: 17 R 435648 3354895
Posts: 6,807
Trading: (6)
HdM
Steve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud of
Default Re: Any Excel gurus around?

__________________
...So don't sit upon the shoreline and say you're satisfied,
Choose to chance the rapids and dare to dance that tide
Steve is offline   Reply With Quote
Old 08-29-2016, 12:57 PM   #12
benedic08
Bob's great grandson
 
benedic08's Avatar
3
 
Join Date: May 2010
First Name: Benedic
Location: South Bay, California
Posts: 506
Trading: (8)
Bolivar
benedic08 has a spectacular aura aboutbenedic08 has a spectacular aura about
Default 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.
__________________
benedic08 is offline   Reply With Quote
Old 08-29-2016, 12:58 PM   #13
pnoon
YNWA
 
pnoon's Avatar
12
 
Join Date: Oct 2008
First Name: Peter
Location: San Diego
Posts: 29,811
Trading: (20)
RA
pnoon has disabled reputation
Default Re: Any Excel gurus around?

Quote:
Originally Posted by Steve View Post

Quote:
Originally Posted by benedic08 View Post
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.
__________________
I'm going recreational.

Be more concerned with your character than your reputation, because your character is what you really are, while your reputation is merely what others think you are.
-John Wooden

2 of 3 Requirements for use of the CA Rolodex: 100 posts/ 60 day membership/participation in trade (trader rating). New members can be added at any time.
pnoon is offline   Reply With Quote
Old 08-29-2016, 06:54 PM   #14
Wharf Rat
I'll get up and fly away
 
Wharf Rat's Avatar
2
 
Join Date: Oct 2008
First Name: Bob
Location: Florence, SC
Posts: 558
Trading: (6)
RA
Wharf Rat will become famous soon enough
Default 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
Wharf Rat is offline   Reply With Quote
Old 08-29-2016, 08:57 PM   #15
benedic08
Bob's great grandson
 
benedic08's Avatar
3
 
Join Date: May 2010
First Name: Benedic
Location: South Bay, California
Posts: 506
Trading: (8)
Bolivar
benedic08 has a spectacular aura aboutbenedic08 has a spectacular aura about
Default Re: Any Excel gurus around?

Quote:
Originally Posted by Wharf Rat View Post
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
__________________
benedic08 is offline   Reply With Quote
Old 08-30-2016, 08:24 AM   #16
stearns
Dogbert Consultant
 
stearns's Avatar
13
 
Join Date: Oct 2008
First Name: Ben
Location: RiNo
Posts: 3,851
Trading: (50)
RA
stearns is just really nicestearns is just really nicestearns is just really nicestearns is just really nicestearns is just really nice
Default Re: Any Excel gurus around?

F4! F4!
__________________
"I have no idea what I'm doing, but I know I'm doing it really really well" - Andy Dwyer
stearns is offline   Reply With Quote
Old 08-30-2016, 10:34 AM   #17
markem
Mostly Harmless
 
markem's Avatar
11
 
Join Date: Oct 2008
First Name: Mark
Location: Casa TJ
Posts: 16,002
Trading: (47)
HUpmann
markem has disabled reputation
Default Re: Any Excel gurus around?

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.



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.

Attached Thumbnails
Click image for larger version

Name:	foo.jpg
Views:	128
Size:	23.1 KB
ID:	17261   Click image for larger version

Name:	foo1.jpg
Views:	110
Size:	35.7 KB
ID:	17262  
markem is offline   Reply With Quote
Old 08-30-2016, 10:36 AM   #18
mhailey
Splitter of Hairs
 
mhailey's Avatar
1
 
Join Date: Mar 2009
First Name: Matt
Location: out west
Posts: 492
Trading: (21)
mhailey has a spectacular aura aboutmhailey has a spectacular aura aboutmhailey has a spectacular aura about
Default Re: Any Excel gurus around?

here is the formula for the average of Yes responses:

=COUNTIF(E2:E5,"Yes")/COUNTA(E2:E5)
__________________
Never try and teach a pig to sing. It frustrates you and irritates the pig.
mhailey is offline   Reply With Quote
Old 08-30-2016, 11:51 AM   #19
Steve
Screen Name Steve
 
Steve's Avatar
 
Join Date: Oct 2008
First Name: Steve
Location: 17 R 435648 3354895
Posts: 6,807
Trading: (6)
HdM
Steve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud ofSteve has much to be proud of
Default 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.
__________________
...So don't sit upon the shoreline and say you're satisfied,
Choose to chance the rapids and dare to dance that tide
Steve is offline   Reply With Quote
Reply

Thread Tools
Display Modes

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
Forum Jump


All times are GMT -6. The time now is 11:57 PM.


Powered by vBulletin® Version 3.7.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
All content is copyrighted jointly by Cigar Asylum and the content provider.