Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-12-2012, 06:42 AM
furface00 furface00 is offline Formula help Windows XP Formula help Office 2007
Novice
Formula help
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default Formula help


Good day,

I am building a spreadsheet to keep track of weekly scores of bridge. I am also using the spreadsheet to calculate yearly averages and to track attendance.

In pulling my data I am having a difficult time trying to create formulas that will match individual scores with names and individual averages with names.

Any help would be appreciated.

Thanks

David
Attached Files
File Type: xlsx Scoresheet test.xlsx (11.8 KB, 9 views)
Reply With Quote
  #2  
Old 02-12-2012, 09:19 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula help Windows XP Formula help Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Hi

does the attached help?
Attached Files
File Type: xlsx Scoresheet test.xlsx (12.5 KB, 9 views)
Reply With Quote
  #3  
Old 02-12-2012, 10:13 AM
furface00 furface00 is offline Formula help Windows XP Formula help Office 2007
Novice
Formula help
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

Thanks Pecoflyer,

I will try to use this for finding all the other names as well.
Reply With Quote
  #4  
Old 02-12-2012, 04:51 PM
furface00 furface00 is offline Formula help Windows XP Formula help Office 2007
Novice
Formula help
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

I tried your suggestion. It work very well in some of the cells.
It worked in the following:
BE19:BE22
BE26:BE29
BB19
I could not get it to work in the following:
BB20:BB22
BB26:BB29

I am getting the #N/A Error. Any ideas?

thanks

David
Reply With Quote
  #5  
Old 02-13-2012, 04:59 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula help Windows XP Formula help Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

See attached

I used a helper column (BC19-BC29) to retrieve the row number in your table.
You can place it anywhere you want and eventually hide it.

It's also a little odd to place the players' names behind the data, usually they are in the first column of the table. This would have been easier for the VLOOKUP function, but not mandatory.

Also be aware that formulae BB19-BB29 are array fomulae and should be committed with Ctrl+Shift+Enter
Attached Files
File Type: xlsx Scoresheet test-1.xlsx (14.8 KB, 4 views)
Reply With Quote
  #6  
Old 02-13-2012, 05:55 AM
furface00 furface00 is offline Formula help Windows XP Formula help Office 2007
Novice
Formula help
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

Many thanks Pecoflyer.

I doubt if I would have been able to figure this out. I was working with Index and Match and going nowhere quickly.

In my actual spreadsheet, I do have the names in the first column, and I wish I had placed that portion in my test sheet as I can now see why it would have been easier to pull the data.

Thank you for all your help.

David
Reply With Quote
  #7  
Old 02-13-2012, 01:59 PM
furface00 furface00 is offline Formula help Windows XP Formula help Office 2007
Novice
Formula help
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

Pecoflyer,

I am sorry to post again in a closed thread. I wasn't sure if I should start a new thread or continue on with this one.

If I should start a new thread please let me know.

I have entered in the various formulas into my working sheet and have encountered a few problems. Mainly, having similar score numbers.

Not sure what to do with this.

Any help would be appreciated.

Thanks

David
Attached Files
File Type: xls Master_weekly_averages.xls (58.5 KB, 6 views)
Reply With Quote
  #8  
Old 02-16-2012, 06:15 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula help Windows XP Formula help Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Furface, with your actual data layout, I cannot come up with a solution. There is probably one, but beyond my Excel knowledge.
I would suggest using a Userform to enter data in columnar form.
It then is quite easy to extract what you need.
Reply With Quote
  #9  
Old 02-16-2012, 11:03 AM
furface00 furface00 is offline Formula help Windows XP Formula help Office 2007
Novice
Formula help
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

Thanks for your reply Pecoflyer.

I am unfamiliar with Userforms however, after looking up what they are I would be prepared to give it a go. Could you recommend a suggested layout for the form?

thanks

Furface
Reply With Quote
  #10  
Old 02-16-2012, 01:01 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula help Windows XP Formula help Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

I would suggest the data entry form under "Download the Sample Data Entry Form" you can find at this page

You can tailor it for your needs and add competitors' names in the Lookuplists sheet.

If it scares you of, let me know, I'll have a go at it.

Once we're there we can work on the stats
Reply With Quote
  #11  
Old 02-26-2012, 05:34 AM
furface00 furface00 is offline Formula help Windows XP Formula help Office 2007
Novice
Formula help
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

Thanks for all your help.
I was able to figure out how a way to pull data with multiple numbers.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help - Look and if and that Corca Excel 2 02-05-2012 09:18 AM
Formula help Help with formula please. AndrewSE Excel 3 04-05-2011 08:50 PM
Formula help PMT Formula OneOleGuy Excel 4 03-05-2011 09:54 AM
Formula help Help with Formula Corca Excel 6 02-22-2010 09:40 PM
Formula help If formula sixhobbits Excel 1 10-02-2009 08:02 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft