Multiple conditions

[jemimapuddleduck]

Hi all,

I'm creating a 'rewards' spreadsheet and have nailed all of the formulas I need, apart from one


I'm getting confused with the multiple conditions I need, and how to put it into a formula.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Rubies Earned[/TD]
[TD="align: center"]1250[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Minimum Tasks Required[/TD]
[TD]Number of Tasks Completed[/TD]
[TD]Structure Level[/TD]
[TD]Rubies Reward[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10[/TD]
[TD]17[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



What I want to be able to calculate is this:




If Structure Level is '5', Reward = 0
If Number of Tasks Completed < minimum tasks required, reward=0
If Number of Tasks Completed > Minimum Tasks Required, Reward = Total Earned as a Team/Number of remaining cases




Hope that makes sense. Can anyone advise, please?


Thanks

 

[jemimapuddleduck]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=IF(OR(C3=5,B3<A3),0,D$1/COUNTIFS($C$3:$C$7,"<>"&5,$B$3:$B$7,"<"&$A$3:$A$7))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Fluff, thank you so much - it worked brilliantly

I really appreciate your help :D

 

[Fluff]

You're welcome & thanks for the feedback