Excel Formula Question
Old
  (#1)
BeiberLvr
AzB Silver Member
BeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond repute
 
BeiberLvr's Avatar
 
Status: Offline
Posts: 7,705
vCash: 500
iTrader: 0 / 0%
Join Date: May 2012
Location: Florida
   
Excel Formula Question - 04-04-2019, 03:49 PM

Anyone here good with Excel?

Column B - Every cell is either a 0 or 1
Column C - Every cell is a random number


I would like a formula that can find the average of a range in Column C, but only when the corresponding cell in Column B is equal to 1.

I thought it would be the AVERAGEIFS formula, but I can't get it to work.

thanks in advance
  
Reply With Quote

Old
  (#2)
mr5994
AzB Silver Member
mr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond repute
 
mr5994's Avatar
 
Status: Offline
Posts: 953
vCash: 500
iTrader: 1 / 100%
Join Date: Apr 2010
   
04-04-2019, 03:56 PM

Quote:
Originally Posted by BeiberLvr View Post
Anyone here good with Excel?

Column B - Every cell is either a 0 or 1
Column C - Every cell is a random number


I would like a formula that can find the average of a range in Column C, but only when the corresponding cell in Column B is equal to 1.

I thought it would be the AVERAGEIFS formula, but I can't get it to work.

thanks in advance
Simple IF THEN statement should accomplish it....Right?

=if(b1=1,average(C:C),)
  
Reply With Quote
Old
  (#3)
BeiberLvr
AzB Silver Member
BeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond reputeBeiberLvr has a reputation beyond repute
 
BeiberLvr's Avatar
 
Status: Offline
Posts: 7,705
vCash: 500
iTrader: 0 / 0%
Join Date: May 2012
Location: Florida
   
04-04-2019, 04:26 PM

Quote:
Originally Posted by mr5994 View Post
Simple IF THEN statement should accomplish it....Right?

=if(b1=1,average(C:C),)
That would take too many nested if statements.
  
Reply With Quote
Old
  (#4)
SmokinJoe46
AzB Silver Member
SmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond reputeSmokinJoe46 has a reputation beyond repute
 
Status: Offline
Posts: 214
vCash: 500
iTrader: 0 / 0%
Join Date: Jan 2019
Location: W NC
   
04-04-2019, 04:34 PM

The syntax of the AVERAGEIF function

=AVERAGEIF(range, criteria, [average_range])

range contains the cells that the function will apply the criteria to. If thereís no average_range argument, these numbers will also be used to calculate the average.

criteria is a logical statement (like ď>4Ē) that determines which numbers are included in the average.

average_range is an optional argument; if itís included, the function will draw the numbers for the average from this range.

https://spreadsheeto.com/averageif/#averageif is a good quick excel source.
HTH
  
Reply With Quote
excel
Old
  (#5)
robertno1pool
AzB Silver Member

robertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond reputerobertno1pool has a reputation beyond repute
 
robertno1pool's Avatar
 
Status: Offline
Posts: 1,212
vCash: 4637
iTrader: 21 / 100%
Join Date: Nov 2005
Location: Houston, Tx
   
excel - 04-04-2019, 05:07 PM

Average of every number = one is one


Robert
CUES: DBK w/ Dragon Carved on Mammoth sleeve, Denali dragon skin, DBK 13 butterflies, Phillippi, Chris Nitti, Paul Mottey, Richard Black, Ernie Martinez, Mike Stacey Titlist Buttterfly conversion; Prulhiere 6-point cue

CASES:[I] JW Hager (2); Ritch Remo Green Ostrich leather; Dennis Swift Barbwire carved;
Jim Murnak , full carved vintage
  
Reply With Quote
Old
  (#6)
Black-Balled
He Rides the Skies
Black-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond reputeBlack-Balled has a reputation beyond repute
 
Black-Balled's Avatar
 
Status: Offline
Posts: 38,635
vCash: 1200
iTrader: 14 / 100%
Blog Entries: 1
Join Date: Mar 2004
Location: This Toilet Earth
  Send a message via AIM to Black-Balled  
04-04-2019, 05:37 PM

Quote:
Originally Posted by robertno1pool View Post
Average of every number = one is one
Keep it to yourself.

Lol
  
Reply With Quote
Old
  (#7)
mr5994
AzB Silver Member
mr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond reputemr5994 has a reputation beyond repute
 
mr5994's Avatar
 
Status: Offline
Posts: 953
vCash: 500
iTrader: 1 / 100%
Join Date: Apr 2010
   
04-04-2019, 06:28 PM

Quote:
Originally Posted by BeiberLvr View Post
That would take too many nested if statements.
I misunderstood......thought you were applying the formula to every row......I think you were on the right track with AVERAGEIF
  
Reply With Quote
Old
  (#8)
sixpack
AzB Silver Member
sixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond repute
 
sixpack's Avatar
 
Status: Offline
Posts: 9,526
vCash: 500
iTrader: 1 / 100%
Blog Entries: 3
Join Date: Sep 2004
Location: CA
   
04-04-2019, 06:46 PM

Quote:
Originally Posted by BeiberLvr View Post
Anyone here good with Excel?

Column B - Every cell is either a 0 or 1
Column C - Every cell is a random number


I would like a formula that can find the average of a range in Column C, but only when the corresponding cell in Column B is equal to 1.

I thought it would be the AVERAGEIFS formula, but I can't get it to work.

thanks in advance
You can also break this out a little. Make another column that uses the IF() statement to copy the value of C into it. Then sum that column and divide by the number of non zero values.


Splitting time between Chicago and San Francisco.

"If voting made any difference they wouldn't let us do it." - Mark Twain
__________________
Email:
Playing Cues: Ernie Martinez, Schon
  
Reply With Quote
Old
  (#9)
our_auctionguy
The Wall.....
our_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond repute
 
our_auctionguy's Avatar
 
Status: Online
Posts: 11,967
vCash: 800
iTrader: 22 / 100%
Join Date: Aug 2007
Location: Boise ID
   
04-04-2019, 07:35 PM

Quote:
Originally Posted by sixpack View Post
You can also break this out a little. Make another column that uses the IF() statement to copy the value of C into it. Then sum that column and divide by the number of non zero values.
Above is on the right track IMO. However I would do it by defining each of the cells in the new column (D) as =IF(B1=1,C1,"") so "" is like a null empty blank and not counted as a numeric value into the average function for column D when B the corresponding B value <>1. The average function should be placed in single cell a in column D row lower than the last row containing values in column C. In the avg, you can avg the values transsferred into column D above simply with the avg(column D1..D-range)

The temptation to just say D values = B*C will not work because the values with 0 in column B will be transferred as numeric values of zero into D when B = 0 and those will get added as a value into the average for column D. Null empty string values placed by "" will be ignored. when B=0 and will not be averaged in.


============================
The Most Inspiring Speech: The Wisdom of a Third Grade Dropout Will Change Your Life
https://youtu.be/Bg_Q7KYWG1g

Last edited by our_auctionguy; 04-04-2019 at 07:41 PM.
  
Reply With Quote
Old
  (#10)
our_auctionguy
The Wall.....
our_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond repute
 
our_auctionguy's Avatar
 
Status: Online
Posts: 11,967
vCash: 800
iTrader: 22 / 100%
Join Date: Aug 2007
Location: Boise ID
   
04-04-2019, 07:42 PM

duped by accident..... anyway..... sometimes you have to add columns in excel to do multiple functional break outs when multi-step logic is involved. You can do filters and use pivot tables, etc that will accomplish the same into separate worksheets.


============================
The Most Inspiring Speech: The Wisdom of a Third Grade Dropout Will Change Your Life
https://youtu.be/Bg_Q7KYWG1g

Last edited by our_auctionguy; 04-04-2019 at 07:48 PM.
  
Reply With Quote
Old
  (#11)
sixpack
AzB Silver Member
sixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond repute
 
sixpack's Avatar
 
Status: Offline
Posts: 9,526
vCash: 500
iTrader: 1 / 100%
Blog Entries: 3
Join Date: Sep 2004
Location: CA
   
04-04-2019, 07:58 PM

Quote:
Originally Posted by our_auctionguy View Post
Above is on the right track IMO. However I would do it by defining each of the cells in the new column (D) as =IF(B1=1,C1,"") so "" is like a null empty blank and not counted as a numeric value into the average function for column D when B the corresponding B value <>1. The average function should be placed in single cell a in column D row lower than the last row containing values in column C. In the avg, you can avg the values transsferred into column D above simply with the avg(column D1..D-range)

The temptation to just say D values = B*C will not work because the values with 0 in column B will be transferred as numeric values of zero into D when B = 0 and those will get added as a value into the average for column D. Null empty string values placed by "" will be ignored. when B=0 and will not be averaged in.
Yeah, I thought of that (null) but wasn’t sure if it would give an error. Long time since I did much excel. That’s why the zero value and then divide by number of nonzero entries. If the nulls will not screw up the zero or the average functions then the way you describe is definitely cleaner.


Splitting time between Chicago and San Francisco.

"If voting made any difference they wouldn't let us do it." - Mark Twain
__________________
Email:
Playing Cues: Ernie Martinez, Schon
  
Reply With Quote
Old
  (#12)
our_auctionguy
The Wall.....
our_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond reputeour_auctionguy has a reputation beyond repute
 
our_auctionguy's Avatar
 
Status: Online
Posts: 11,967
vCash: 800
iTrader: 22 / 100%
Join Date: Aug 2007
Location: Boise ID
   
04-04-2019, 08:04 PM

Quote:
Originally Posted by sixpack View Post
Yeah, I thought of that (null) but wasnít sure if it would give an error. Long time since I did much excel. Thatís why the zero value and then divide by number of nonzero entries. If the nulls will not screw up the zero or the average functions then the way you describe is definitely cleaner.
This was done in Googel Sheets with same formulas.
Attached Images
  


============================
The Most Inspiring Speech: The Wisdom of a Third Grade Dropout Will Change Your Life
https://youtu.be/Bg_Q7KYWG1g

Last edited by our_auctionguy; 04-04-2019 at 08:06 PM.
  
Reply With Quote
Old
  (#13)
sixpack
AzB Silver Member
sixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond reputesixpack has a reputation beyond repute
 
sixpack's Avatar
 
Status: Offline
Posts: 9,526
vCash: 500
iTrader: 1 / 100%
Blog Entries: 3
Join Date: Sep 2004
Location: CA
   
04-04-2019, 08:13 PM

Quote:
Originally Posted by our_auctionguy View Post
This was done in Googel Sheets with same formulas.
Cool! Looks good.


Splitting time between Chicago and San Francisco.

"If voting made any difference they wouldn't let us do it." - Mark Twain
__________________
Email:
Playing Cues: Ernie Martinez, Schon
  
Reply With Quote
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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



Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
vBulletin Security provided by vBSecurity (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.