Calculate cell only when column header equals a certain value


Calculate cell only when column header equals a certain value



First time poster so any would be greatly appreciated.



I would like to perform calculations on a given row only when the column header contains a value.


Column A
**Name**
Column B
**Test 1**
Column C
**H1**
Column D
**H2**
Column E
**Test 2**



The above example will better explain what I'd like to do. I have a series of columns, some with test results and with homework results. Tests and Homework results can be in any order. What I would like to do is perform calculations such average, sum etc based on whether it is a homework or test. So that for example I would like to average all the homework results for a pupil (on a given row).



Is this possible and if so how. I am using Excel 2010 if that helps.





Maybe an example with figures will help describe your problem?
– Jerry
Oct 7 '13 at 8:56





You can look for formulas like SUMIF or SUMPRODUCT. Why don't you give it a try and then post back with what you tried and where are you stuck?
– Siddharth Rout
Oct 7 '13 at 9:05


SUMIF


SUMPRODUCT




2 Answers
2



You can use the SUMIF and COUNTIF functions. Lets says your worksheet looks like this:


Name Test 1 H1 H2 Test 2
John 10 20 30 20



Then you can get these values:


No. of Tests No. of H Sum of Test Sum of H Avg of Test Avg of H
2 2 30 50 15 25



By doing this:



The Sum Testis calculated using =SUMIF(B1:E1;"Test *";B2:E2) and the No. of Testsis calculated using =COUNTIF(B1:E1;"Test *") (to get the number of Testin the header range).


Sum Test


=SUMIF(B1:E1;"Test *";B2:E2)


No. of Tests


=COUNTIF(B1:E1;"Test *")


Test



The Avg of...is just the simple division of "Sum of" and "No. of" values.


Avg of...



Of course you will have to adjust the range used to match the actual range of values in the header and value rows.



I ended up using AVERAGEIF which seems to give me the correct values. Because It has three qualifiers the first most importantly is the range which I can reference the headers and then I can provide the criteria so where header is "H*" etc and then the rows to calculate the average.



This is the formula that I used:



=AVERAGEIF(Table1[[#Headers],[HM1]:[HM3]],"H*",Table1[@[HM1]:[HM3]])



Now I just need to work on a count which is similar to the above!



Just a further update to this. I managed to also work out the count using the COUNTIFS function. What I didn't realise originally was the IFS were inclusive so it included the first IF AND the second IF.



This is the formula I eventually used:



=COUNTIFS(Table1[#Headers],"H*",Table1[@[HM1]:[HM3]],">0")



Thanks All.






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Comments

Post a Comment

Popular posts from this blog

paramiko-expect timeout is happening after executing the command

paramiko-expect timeout is happening after executing the command Can someone help me why timeout is happening after executing the command .I am trying to SSH to a machine and execute a command.I want to store the result of the executed command . I have referred paramiko-timeout but i didn't get my expected result. import traceback try: import paramiko except ImportError: raise Exception("Please install paramiko , pip install paramiko") import pexpect from paramiko_expect import SSHClientInteraction def main(): HOSTNAME = "minion5.net" USERNAME = "root" PASSWORD = "help@432" PROMPT = "root@*:~$s+" try: client = paramiko.SSHClient() client.load_system_host_keys() client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) client.connect(hostname=HOSTNAME, username=USERNAME, password=PASSWORD) with SSHClientInteraction(client, timeout=20, display=True) as interact: ...
Read more

Possible Unhandled Promise Rejection (id: 0): ReferenceError: user is not defined ReferenceError: user is not defined

Possible Unhandled Promise Rejection (id: 0): ReferenceError: user is not defined ReferenceError: user is not defined I have stored data user with AsyncStorage when i want to get it, it renders null in alert and the above error in the console,i don't know why. I can not see why it returns null with user but token all is well profile.js : import React, {Component} from 'react'; import { StyleSheet, Text, View, Image, TouchableOpacity,AsyncStorage} from 'react-native'; import { Container, Content, Header,Right, Left, Body} from 'native- base'; import Feather from 'react-native-vector-icons/Feather'; export default class Profile extends Component { constructor (props){ super(props); this.state = { user: null } } componentDidMount(){ this.fetchData(); } fetchData = async() =>{ let username = await AsyncStorage.getItem('user') this.setState({ user: username }) ...
Read more

Opening a url is failing in Swift

Opening a url is failing in Swift breakpointing inside the callback and logging success shows false. I added itms-apps to my plist: LSApplicationQueriesSchemes and put this in my app delegate: func application(_ app: UIApplication, open url: URL, options: [UIApplicationOpenURLOptionsKey : Any] = [:]) -> Bool { return true } func moreTapped() { let url = NSURL(string: "itms-apps://itunes.com/developer/quantum-productions/id979315877") if #available(iOS 10.0, *) { UIApplication.shared.open(url! as URL, options: [:], completionHandler: {(success: Bool) in }) } else { UIApplication.shared.openURL(url! as URL) } } Are you testing on a real device? – rmaddy Jun 21 at 23:57 I try on a device, get "Cannot Connect to App Store". Device is online ...
Read more