skipping a cell when calculating a total Closed

Question

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

Configuration: Windows XP / Safari 532.0

rizvisa1 · Answer

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.

starhawk85 · Answer

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

starhawk85 · Answer

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

rizvisa1 · Answer

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

starhawk85 · Answer

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..

rizvisa1 · Answer

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.

starhawk85 · Answer

perfects thank you tons!
StarhawkArts.com 
Art Has No Limits!

Skipping a cell when calculating a total

7 responses

Similar discussions

Subscribe To Our Newsletter!