Skipping a cell when calculating a total

Closed
Starhawk85 - Apr 23, 2010 at 06:34 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 25, 2010 at 08:56 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 24, 2010 at 06:32 AM
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
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 23, 2010 at 07:30 PM
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.
0
starhawk85 Posts 16 Registration date Friday April 23, 2010 Status Member Last seen April 27, 2010 1
Apr 23, 2010 at 08:24 PM
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
0
starhawk85 Posts 16 Registration date Friday April 23, 2010 Status Member Last seen April 27, 2010 1
Apr 23, 2010 at 08:36 PM
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
0

Didn't find the answer you are looking for?

Ask a question
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 23, 2010 at 10:26 PM
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
0
starhawk85 Posts 16 Registration date Friday April 23, 2010 Status Member Last seen April 27, 2010 1
Apr 23, 2010 at 11:01 PM
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..
0
starhawk85 Posts 16 Registration date Friday April 23, 2010 Status Member Last seen April 27, 2010 1
Apr 25, 2010 at 08:54 PM
perfects thank you tons!
StarhawkArts.com
Art Has No Limits!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 25, 2010 at 08:56 PM
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.
0