IF statement keeps giving me "#VALUE!" error

[jybbbyl]

I'm trying to get Excel to automatically grade a 79 question assessment following the answer input. Below is the formula, but it keeps giving me the #VALUE! error. I've input the same formula using a 10 question assessment and it grades it just fine. When I input the value for the 79 question assessment is when I run into this problem. Any Help???

=IF(C4=$C$60,0.0126582278481012,0)+IF(D4=$D$60,0.0126582278481012,0)+IF(E4=$E$60,0.0126582278481012,0)+IF(F4=$F$60,0.0126582278481012,0)+IF(G4=$G$60,0.0126582278481012,0)+IF(H4=$H$60,0.0126582278481012,0)+IF(I4=$I$60,0.0126582278481012,0)+IF(J4=$J$60,0.0126582278481012,0)+IF(K4=$K$60,0.0126582278481012,0)+IF(L4=$L$60,0.0126582278481012,0)+IF(M4=$M$60,0.0126582278481012,0)+IF(N4=$N$60,0.0126582278481012,0)+IF(O4=$O$60,0.0126582278481012,0)+IF(P4=$P$60,0.0126582278481012,0)+IF(Q4=$Q$60,0.0126582278481012,0)+IF(R4=$R$60,0.0126582278481012,0)+IF(S4=$S$60,0.0126582278481012,0)+IF(T4=$T$60,0.0126582278481012,0)+IF(U4=$U$60,0.0126582278481012,0)+IF(V4+$V$60,0.0126582278481012,0)+IF(W4+$W$60,0.0126582278481012,0)+IF(X4+$X$60,0.0126582278481012,0)+IF(Y4+$Y$60,0.0126582278481012,0)+IF(Z4+$Z$60,0.0126582278481012,0)+IF(AA4+$AA$60,0.0126582278481012,0)+IF(AB4+$AB$60,0.0126582278481012,0)+IF(AC4+$AC$60,0.0126582278481012,0)+IF(AD4+$AD$60,0.0126582278481012,0)+IF(AE4+$AE$60,0.0126582278481012,0)+IF(AF4+$AF$60,0.0126582278481012,0)+IF(AG4+$AG$60,0.0126582278481012,0)+IF(AH4+$AH$60,0.0126582278481012,0)+IF(AI4+$AI$60,0.0126582278481012,0)+IF(AJ4+$AJ$60,0.0126582278481012,0)+IF(AK4+$AK$60,0.0126582278481012,0)+IF(AL4+$AL$60,0.0126582278481012,0)+IF(AM4+$AM$60,0.0126582278481012,0)+IF(AN4+$AN$60,0.0126582278481012,0)+IF(AO4+$AO$60,0.0126582278481012,0)+IF(AP4+$AP$60,0.0126582278481012,0)+IF(AQ4+$AQ$60,0.0126582278481012,0)+IF(AR4+$AR$60,0.0126582278481012,0)+IF(AS4+$AS$60,0.0126582278481012,0)+IF(AT4+$AT$60,0.0126582278481012,0)+IF(AU4+$AU$60,0.0126582278481012,0)+IF(AV4+$AV$60,0.0126582278481012,0)+IF(AW4+$AW$60,0.0126582278481012,0)+IF(AX4+$AX$60,0.0126582278481012,0)+IF(AY4+$AY$60,0.0126582278481012,0)+IF(AZ4+$AZ$60,0.0126582278481012,0)+IF(BA4+$BA$60,0.0126582278481012,0)+IF(BB4+$BB$60,0.0126582278481012,0)+IF(BC4+$BC$60,0.0126582278481012,0)+IF(BD4+$BD$60,0.0126582278481012,0)+IF(BE4+$BE$60,0.0126582278481012,0)+IF(BF4+$BF$60,0.0126582278481012,0)+IF(BG4+$BG$60,0.0126582278481012,0)+IF(BH4+$BH$60,0.0126582278481012,0)+IF(BI4+$BI$60,0.0126582278481012,0)+IF(BJ4+$BJ$60,0.0126582278481012,0)+IF(BK4+$BK$60,0.0126582278481012,0)+IF(BL4+$BL$60,0.0126582278481012,0)+IF(BM4+$BM$60,0.0126582278481012,0)+IF(BN4+$BN$60,0.0126582278481012,0)+IF(BO4+$BO$60,0.0126582278481012,0)+IF(BP4+$BP$60,0.0126582278481012,0)+IF(BQ4+$BQ$60,0.0126582278481012,0)+IF(BR4+$BR$60,0.0126582278481012,0)+IF(BS4+$BS$60,0.0126582278481012,0)+IF(BT4+$BT$60,0.0126582278481012,0)+IF(BU4+$BU$60,0.0126582278481012,0)+IF(BV4+$BV$60,0.0126582278481012,0)+IF(BW4+$BW$60,0.0126582278481012,0)+IF(BX4+$BX$60,0.0126582278481012,0)+IF(BY4+$BY$60,0.0126582278481012,0)+IF(BZ4+$BZ$60,0.0126582278481012,0)+IF(CA4+$CA$60,0.0126582278481012,0)+IF(CB4+$CB$60,0.0126582278481012,0)+IF(CC4+$CC$60,0.0126582278481012,0)

 

[ghrain22]

Holy Moly....I would suggest setting up your grading VASTLY differently.

I will mention that later in your formula that you have if statements that look like:
If(cell+cell,.0126,0)
I'm going to assume those you want to be "=". But I don't know what your sheet looks like.

However if it works in other cells properly. I'm going to assume theres a reference that didn't get changed to an absolute reference somehwere.

 

[ghrain22]

Holy Moly....I would suggest setting up your grading VASTLY differently.

I will mention that later in your formula that you have if statements that look like:
If(cell+cell,.0126,0)
I'm going to assume those you want to be "=". But I don't know what your sheet looks like.

As for an alternative try something like:
=countif(Answer Range,"="&Grading Range)*0.0126582278481012

 

[platonic567]

Wow that formula is really long...

If you post some sample data and desired result, someone would probably be able to come up with a shorter more effective solution.

 

[DeezNuts]

question for the gurus would the new formula be similar to this =IF(C4:CC4=$C$60:$CC$60,0.0126582278481012,0) Was bit confused by the = for the first 10 than a + for the rest.

 

[jybbbyl]

OOOS!!! Yeah the "+" was supposed to be an "=". Still doesn' work though. LOL out of frustration.

Holy Moly....I would suggest setting up your grading VASTLY differently.

I will mention that later in your formula that you have if statements that look like:
If(cell+cell,.0126,0)
I'm going to assume those you want to be "=". But I don't know what your sheet looks like.

However if it works in other cells properly. I'm going to assume theres a reference that didn't get changed to an absolute reference somehwere.

 

[johnmpl]

Hi to all of you!

Try this formula:
=0.0126582278481012*SUMPRODUCT(--(C4:CC4=C60:CC60))

Blessings!

 

[ktcharm]

Try this in cell C61, =IFERROR(IF(C$4=C$60,0.0126582278481012,0),0) and copy it across to cell CC61.

If you have nothing for B61, =SUM(C$61:CC$61). Cell B61 should be your desired result, not sure If that is what you are asking.