Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-17-2020, 09:41 AM
Intruder Intruder is offline How to use lookup functions to calculate statistical paras using named ranges Windows XP How to use lookup functions to calculate statistical paras using named ranges Office 2007
Advanced Beginner
How to use lookup functions to calculate statistical paras using named ranges
 
Join Date: Dec 2010
Posts: 59
Intruder is on a distinguished road
Default How to use lookup functions to calculate statistical paras using named ranges


I have an Excel table attached with data from study in small animals.
Each line includes nr, gender, code and 3 body weights measured on Day0, D1 and D2.
I like to take averages, counts (n) and stdevs for each code-group and on each Day using named ranges.
However, calculating averages is no problem but for count and stdev there is a problem as I may need to used lookup functions.

Can someone help me with this?
Problem fields are marked in yellow backgound
In the table, right from the original data, the same data or copied and sorted by code to easily calculate above parameters using simple formulas for comfirmation of used formulas.

Thanks in advance
Roland
Attached Files
File Type: xlsx for forum.xlsx (18.7 KB, 6 views)
Reply With Quote
  #2  
Old 04-17-2020, 03:44 PM
p45cal's Avatar
p45cal p45cal is offline How to use lookup functions to calculate statistical paras using named ranges Windows 10 How to use lookup functions to calculate statistical paras using named ranges Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

See attached.
3 solutions.
Attached Files
File Type: xlsx msofficeforums44765for forum.xlsx (20.7 KB, 10 views)
Reply With Quote
  #3  
Old 04-18-2020, 01:43 AM
Intruder Intruder is offline How to use lookup functions to calculate statistical paras using named ranges Windows XP How to use lookup functions to calculate statistical paras using named ranges Office 2007
Advanced Beginner
How to use lookup functions to calculate statistical paras using named ranges
 
Join Date: Dec 2010
Posts: 59
Intruder is on a distinguished road
Default

Great, thanks tp p45cal
Reply With Quote
  #4  
Old 05-05-2020, 03:27 AM
CJustin CJustin is offline How to use lookup functions to calculate statistical paras using named ranges Windows 10 How to use lookup functions to calculate statistical paras using named ranges Office 2019
Novice
 
Join Date: May 2020
Posts: 9
CJustin is on a distinguished road
Default

Hey, would you relay the solution to be accessible to everyone that tumbles on this question?
I had a similar stdev problem.
Reply With Quote
  #5  
Old 05-05-2020, 03:53 AM
p45cal's Avatar
p45cal p45cal is offline How to use lookup functions to calculate statistical paras using named ranges Windows 10 How to use lookup functions to calculate statistical paras using named ranges Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

  1. Primary solution is just the formulae in the yellow cells in the vicinity of cells E41:G47.
  2. The second solution at C54:G68 is pivot table data grabbed from the pivot table at cell Q37, which in turn is based on the table at cell B2.
  3. The third solution at cell A70 is a pivot table output of Power Query. To see how this has been processed, go to the Data tab of the ribbon, click on Queries & Connections in the Queries & Connections section. A pane will pop up where you will see one query, Table1, right-click and choose Edit to see the steps taken.
I won't go into further details (it takes far too long) unless you have specific questions about one or two aspects of these suggested solutions.
Reply With Quote
  #6  
Old 05-05-2020, 03:53 AM
macropod's Avatar
macropod macropod is offline How to use lookup functions to calculate statistical paras using named ranges Windows 7 64bit How to use lookup functions to calculate statistical paras using named ranges Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by CJustin View Post
Hey, would you relay the solution to be accessible to everyone that tumbles on this question?
I had a similar stdev problem.
You could try downloading the attachment to post #2...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-08-2020, 01:21 AM
Intruder Intruder is offline How to use lookup functions to calculate statistical paras using named ranges Windows XP How to use lookup functions to calculate statistical paras using named ranges Office 2007
Advanced Beginner
How to use lookup functions to calculate statistical paras using named ranges
 
Join Date: Dec 2010
Posts: 59
Intruder is on a distinguished road
Default Want to extend formulae from previous attempt for means, N and sd

Thanks to p45cal this problem has been solved. However, I want to expand the formulae to individual genders male and female. Calculations of means and N are ok, I'm still suffering with sd.
I attached the updated Excel with the cells for formulae needing support in red color.
Any help?
Roland
Attached Files
File Type: xlsx msofficeforums44765for forum.xlsx (21.0 KB, 6 views)
Reply With Quote
  #8  
Old 05-08-2020, 11:55 AM
p45cal's Avatar
p45cal p45cal is offline How to use lookup functions to calculate statistical paras using named ranges Windows 10 How to use lookup functions to calculate statistical paras using named ranges Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

see attached
Attached Files
File Type: xlsx msofficeforums44765for forum_v2.xlsx (21.4 KB, 7 views)
Reply With Quote
  #9  
Old 05-09-2020, 10:50 AM
Intruder Intruder is offline How to use lookup functions to calculate statistical paras using named ranges Windows XP How to use lookup functions to calculate statistical paras using named ranges Office 2007
Advanced Beginner
How to use lookup functions to calculate statistical paras using named ranges
 
Join Date: Dec 2010
Posts: 59
Intruder is on a distinguished road
Default

Perfect, big thans, appreciated, regards
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use of named ranges based on cell content Intruder Excel 12 02-25-2019 09:42 AM
accessing individual row data in named ranges MimiCush Excel 25 09-27-2018 01:01 PM
How to use lookup functions to calculate statistical paras using named ranges Named Ranges Help SavGDK Excel 5 05-01-2017 09:41 AM
How to use named ranges in excel vba? bosve73 Excel Programming 4 01-25-2012 09:26 AM
Dynamic Named Ranges using text hannu Excel 0 06-22-2010 04:42 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:24 PM.


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