1

To determine which student gets a "bonus" mark of 5, a GRAD student must be PRESENT in both SEMINARS.

An undergrad student, UGRAD, must have been present in either SEM 1 or SEM 2.

If a grad student had attended on one or no seminars, bonus=0. If an ugrad student attended none, bonus=0.

https://i.stack.imgur.com/gfcPX.png

I came up with formulas for both undergrad, ugrad and for grad students but am having difficulty combing the functions into one as required.

for GRAD I used:

=IF(AND(Status="grad",Sem_1="present",Sem_2="present"),5,0)

for UGRAD I used:

=IF(OR(Sem_1="present",Sem_2="present"),5,0)

** EDIT only one formula may be used

Improve this question
  • Homework assumes you've learned the lessons yourself. – music2myear Nov 28 '16 at 23:30
  • 1
    I'm voting to close this question as off-topic because it is a homework help request where OP has not begun resolving the question themselves. – music2myear Nov 28 '16 at 23:30
  • Do you mean that the formulas are working, but you want one that combines them and works for both? In that case, just make another IF that checks the status for either grad or ugrad and then run one code if true and the other if false. – Christofer Weber Nov 28 '16 at 23:38
  • 1
    I don't feel that fair as I've tried several formulas, rearranging them after watching videos and excel tutorials and nothing seems to make sense to me, I showed the steps by presenting two seperate formulas I used for the two seperate statuses and one of my attempts of merging the two formulas together, one of many, however to the same result. If you feel that was that is reasonable but I really hope others don't. – user669017 Nov 28 '16 at 23:40
  • Use two different cells and hide the computation cells then just A1 AND B1 – Ramhound Nov 28 '16 at 23:43
2

With If you can use:
=IF(AND(Status="grad",Sem_1="present",Sem_2="present"),5,IF(AND(Status="ugrad",OR(Sem_1="present",Sem_2="present")),5,0))

I have combined your 2 ifs in one by adding and status, it is a nested if in the false part you can start a second statement. I tried it with normal references:
=IF(AND(B3="grad",C3="present",D3="present"),5,IF(AND(B3="ugrad",OR(C3="present",D3="present")),5,0))
with the same display of your picture
column B is status column C and D Sem_1 and Sem_2

Improve this answer
-1

With no IF statements, just for fun. In cell H5:

=5*(0<(Status="grad")*((Sem_1="present")*(Sem_2="present"))+(Status="ugrad")*((Sem_1="present")+(Sem_2="present")))

enter image description here

I would further improve this worksheet by changing any status that is present to 1 and absent to 0. You can use custom number formats to make them appear as "present" and "absent" (namely, by selecting those cells, hitting Ctrl+1, going to Custom, and specifying "present";;"absent" as the format.

enter image description here

This will make the table look exactly the same but the formula gets much shorter:

=5*(0<(Status="grad")*(Sem_1+Sem_2)+(Status="ugrad")*(Sem_1*Sem_2))

enter image description here

This is also easier to maintain -- if you ever want to change, say, from "present/absent" to "attended/missed" you won't have to edit the formula. In other words, the formula doesn't make assumptions about the wording of the Sem_1 and Sem_2 cells, only their values. It also makes easier to count how many students were present/absent, for example.

Improve this answer
  • Use Evaluate Formula to check how the formula works. Alt, M, V will pop up the evaluate formula dialog, and you can it Alt+E to go through each step. – airstrike Nov 29 '16 at 0:07
  • 1
    Firstly, thank you for going into all that effort to help with the question, I truly appreciate it. In regards to the OP, the column "J" contains the results I would want, the formula you provided wasn't able to achieve that. I was happy to learn about ability to see how formulas work step by step and I'm sure that will be useful to me in later work. I feel in this scenario IF statements are most probably needed. – user669017 Nov 29 '16 at 0:14
  • IF statements are not needed. I misread the question and missed the column J. Basically you just have to flip the + and * between Sem_1 and Sem_2. I'll update the answer right away. – airstrike Nov 29 '16 at 0:15
  • 1
    The wonders of excel I imagine. Always more than one way to go about a scenario, thanks for the help Andre!! The evaluate tool definitely helps understand how things work, I'll try to come up the an IF branch from what I've learned here (definitely not necessary but couldn't hurt). Have a great day! – user669017 Nov 29 '16 at 0:27
  • You're welcome. Let me know if you get stuck coming up with the IFs and I'll be glad to help. – airstrike Nov 29 '16 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy