Skipping a cell when calculating a total [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
i have a series of codes back and forth it seems a bit insane.
but what im wanting is for cell b1 c1 d1 e1 and etc to add together wich i have the following for this to return as my average
it looks like this >>>> =SUM(AVERAGE(B11:H11))/SUM(AVERAGE($B$5:$H$5))

now the only problem im running into is if b1 has a number in it but c1:e1 dose not have numbers

i want them to refer to another cell that dose have number while still viewing as blank

but excluding the blank cells within the final number

im working with %'s

any way its not working to well for me but this is the closest i have gotten to it working

=SUM(IF(B9="",B9,$B$5),IF(C9="",C9,$C$5),IF(D9="",D9,$D$5),IF(E9="",E9,$E$5),IF(F9="",F9,$F$5),IF(G9="",G9,$G$5),IF(H9="",H9,$H$5)/SUM($B$5:$H$5))

but the final % is way off.. and im not sure if it is truely not counting the blank cells

ANY HELP WOULD BE GREATLY APPRECIATED

7 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Then in this case, use this formula

=SUM(B6:H6)/SUMPRODUCT((B$2:H$2) * (B6:H6<>""))

or

=SUM(B6:H6)/SUMIF(B6:H6,"<>",B$2:H$2)

This finds the average based on the the cells that are populated.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Posts
16
Registration date
Friday April 23, 2010
Status
Member
Last seen
April 27, 2010
1
i have cells [_] [_] [_] following this direction>>>> e1-f1-g1

if it looks like this [23] [_] [_] i need the formula to skip over the blank cells when calculating to give me my total
they can not have 0 in them because 0 is being used other wise

but if the cells are to have #'s like 5 or 10 or 12 ant etc. in them when added like so [23] [12] [_] they need to be added to the calculation to the total while still avoiding adding the blank cell to the total

it needs to only calculate the total within the cells that have numbers in them so its not adding the blank cells as part of the total .... its kina complicated i guess
Posts
16
Registration date
Friday April 23, 2010
Status
Member
Last seen
April 27, 2010
1
here it is the section im trying to get this to work on is in I there are a few diff codes in each one i have a bunch more but these are what come closest to working but its just not happening ...
https://authentification.site/files/22094257/gradebook.xlsb
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
You have so many different formulas in it. what exactly are you trying to do

From what I can see,
either you are trying to add to total score and divide it by total maximum possible score
=sum(b8:h8) /sum(b2:h2)

or you are trying to weighted score
=SUMPRODUCT((B8:H8)/(B$2:H$2))/COUNT(B$2:H$2)

If you are looking for some thing else, could you explain about what sort of calculation
Posts
16
Registration date
Friday April 23, 2010
Status
Member
Last seen
April 27, 2010
1
reason for the diff formulas is just my testing them out

i am wanting to add to total score and divide it by total maximum possible score
but if there is a blank cell i need to read it as if it did not exist
or
if there is a blank cell i want it to read the possible score in the total
to provide a current grade...

for example assignment one has been done and now currently working on assignment 2
lets say assignment 1 has a possible points of 30 and i have received 30 points for it my grade should read as 100% A even though assignment 2,3,4,5..etc have no points entered in.

really simply the question asked is what is my grade so far with the assignments we have done.
i guess i need the cells that have nothing entered to = the possible points without showing in the individual cells. and still get a correct % and grade letter..
Posts
16
Registration date
Friday April 23, 2010
Status
Member
Last seen
April 27, 2010
1
perfects thank you tons!
StarhawkArts.com
Art Has No Limits!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Post your book. Also put the formula that you are using. as well hard code the values that are the formula should have given you.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!