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

 

[etaf]

Re: Multiple conditions help please

what does this mean

Total Earned as a Team/Number of remaining cases

where is that on the example ?

 

[jemimapuddleduck]

Re: Multiple conditions help please

what does this mean
where is that on the example ?

Sorry, my bad. It should read Rubies Earned/number of remaining cases

 

[Fluff]

Re: Multiple conditions help please

Where is the number of remaining cases?

 

[jemimapuddleduck]

Re: Multiple conditions help please

It would be any that aren't excluded based on the first two conditions

So, in order to receive a reward, they would need to meet all conditions, but conditions 1 and 2 should exclude some

I'm not convinced it can be done as one formula, but thought I'd try my luck!

 

[Finalfight40]

Re: Multiple conditions help please

Not 100% clear on what is needed here but this is my guess?

=IF(OR(C3=5, B3 < A3), 0, ($D$1/COUNT($A$3:$A$7)))

If not then as fluff said below please.<a3), 0,="" ($d$1="" count($a$3:$a$7)))<="" html=""></a3),>

 

[Fluff]

Re: Multiple conditions help please

Still not with you, can you show expected results, along with how you calculated them?

 

[jemimapuddleduck]

These are the results I calculated manually:


[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]625[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]625[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10[/TD]
[TD]17[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Member on row 5 was not entitled to rewards as:

Number of Tasks Completed < Minimum Tasks Required

Member on row 6 was not entitled to rewards as:

Structure Level = 5

Member on row 7 was not entitled to rewards as:

Number of Tasks Completed < Minimum Tasks Required

 

[jemimapuddleduck]

And the remaining rubies earned were split equally between the members who were entitled

 

[Fluff]

In that case try

Excel 2013/2016
	A	B	C	D
1			Rubies Earned	1250
2	Minimum Tasks Required	Number of Tasks Completed	Structure Level	Rubies Reward
3	9	9	4	625.0
4	9	12	4	625.0
5	9	0	4	0.0
6	10	17	5	0.0
7	8	2	3	0.0
search

Cell Formulas
Range	Formula
D3	=IF(OR(C3=5,B3),0,D$1/COUNTIFS($C$3:$C$7,"<>"&5,$B$3:$B$7,"<"&$A$3:$A$7))