• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SO MANY IFs

A

Ariff Chowdhury

New Member
Hi,

This is my first post in this platform...factually in any platform other than Faceboook:)


I have stuck with formula that i will try mys level best to make you people understand.


I have table with 6 columns

1 - Quiz 1

2 - Midterm

3 - Quiz 2

4 - Assignment

5 - Class Participation

6 - Final Marks


I have to update the "Marks Obtained" against each respective heads (fields) in tabular format e.g. Quiz 1, Midterm and Quiz 2 and so on,I need a formula that provide me the result in percentage when i update "Marks Obtained" in any of these 6 columns and it should divide the number with according to given "Maximum Marks" and give me the percentage in result.


Criteria of "Maximum Marks" which is to be divided by "Marks Obtained"

If only Quiz 1 marks are entered then it should divide it by Maximum Marks 5

If only Mid Term marks are entered then it should divide it by Maximum Marks 20

If only Quiz 2 marks are entered then it should divide it by Maximum Marks 5

If only Assign. marks are entered then it should divide it by Maximum Marks 15

If only Class P. marks are entered then it should divide it by Maximum Marks 5

If only Final Exam marks are entered then it should divide it by Maximum Marks 50


BUT IF...

Quiz 1 as well as Mid Term both marks are enter the it should divide it by 25 (Because Quiz 1 has 5 maximum marks and Mid Term has 20... So 20+5 = 25).However If Quiz 1 marks are not entered and Midterm or any other fields marks are entered then it should calculate the number according to the given Maximum Marks.


Furthermore,Any of these columns' can be updated randomly i mean there is no sequence for entering the marks like Quiz 1 and then Mid Term and then Quiz 2 (Not Sequence data entry)...it is not like that....User may enter Quiz 1 marks first or may leave it blank and enter the Quiz 2 marks(in this case it should divide it by 5 and give the percentage if other fields are blank) or may enter Final Marks only (in this case it should divide the number by 50)


If you please answer my query

I would be grateful

Thanks

Ariff
 
Ariff


Welcome to the Chandoo.org Forums


Could you please post a sample workbook with maybe a few worked examples of marks and results


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Mr Chowdhury,


I am also waiting for your sample upload..


Please

[pre]
Code: 
0.05	0.20	0.05	0.15	        0.05	0.50
Quiz 1	Midterm	Quiz 2	Assignment	Class P	Final Marks	Maximum Marks
===============================================================
75		50	35			                Enter Formula Here
[/pre]
I have created a helper Row.. at Row 1..(A1 to F1)

=SUMPRODUCT($A$1:$F$1,$A3:$F3)

Drag the same to get other result..


We may help you better, if you upload a sample workbook.. :)


Regards,

Deb
 
Dear Hui and Roy...

Below is the link and both leads to same path...


https://skydrive.live.com/redir?resid=F6306DECDB515F3B!318&authkey=!AJpG58dbqNDOgPU


http://sdrv.ms/TUq4bF


And Roy I have checked your formula =SUMPRODUCT($A$1:$F$1,$A3:$F3) it is not providing intended result...after applying formula in valid location I entered 45 for Final Term marks, the formula resulted 25 (I think it's saying 25%) which is wrong because,maximum marks of final term is 50 so it should divide 45 by 50 and result should be 90%.


And George your formula resulted somehow the same wrong figures as well.
 
Hi Ariff,


Can you please try the below formula in J7


Code: 
=SUM($C7:$H7)/SUMPRODUCT(($C7:$H7>0)*$C$4:$H$4)


dont forget to format the cell as %age.. ;)


Please let us know if its working as per requirement..


Regards,

Deb
 
WOW Deb you're awesome it seems to be working partially... one thing will make it perfect..what if a student get 0 marks in any of the column ? As i entered 5 in Quiz1 and 0 in Quiz 2.. it is giving me 100% though it should have resulted 50%. I have tried to amend your formula by adding "=" after ">" sign. but it didn't work.


=SUM($K66:$P66)/SUMPRODUCT(($K66:$P66>=0)*$K$1:$P$1)
 
Thanks Ariff,


Just a little modification in the formula..

Code: 
=SUM($K66:$P66)/SUMPRODUCT((ISNUMBER($K66:$P66)*$K$1:$P$1))


BTW.. its still not perfect,

You may also add a check if Marks are not greater than Max Marks.. and No -ve marks also.. :)


Regards,

Deb
 
Thanks Deb! Perfect in the sense that it gives me the desired result.

I think greater and -ve marks can be controlled through validation which we will get it done,:)..don't worry


But you people are really great!


Thanks a lot!
 
Ariff!

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.


Regards,

Deb
 
You must log in or register to reply here.
Back
Top