Try to be more precise

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

Hello,



I will try to be precise about what I am trying to achieve.



My formula. Which does not work and asks for another ( or ). but I think
even if I put the ( or ) in it may not achieve what I want.



=IF(AND(SUM($P$53:$Q$53)>449,L30="N",IF(AND(SUM($P$53:$Q$53)>199,L30="D"),SUM(P30:Q30)/10),"")



Ok now to explain exactly what I need.



Cells P53 and Q53 entries sum from the P column and the Q column
respectively from row 30 to row 52



Cell L30 can have a "D" or an "N" placed in it, as can cells L31 to L52



for row 30, If the L30 contains a "D" then as long as the sum of P53 plus
Q53 is above 199 then outcome in the cell with the formula will be 10% of
the sum of P30 plus Q30.



for row 30, if however L30 contains an "N" then the value of P53 plus Q53
must be above 499 before the result in the cell with the formula is 10% of
the sum of P30 plus Q30.



If Cell L30 contains a "D" and the total of P53 plus Q53 is below 199 then
the result in the cell with the formula will be zero.



If Cell L30 contains a "N" and the total of P53 plus Q53 is below 499 then
the result in the cell with the formula will be zero.



This formula then must extend from row 30 to row 52 results respective of
the row that they are, still relying on the fixed ($P$53:$Q$53). (I know how
to drag these down)



Sometimes there will be no entry in row 30 but entries in say row 41 to row
45 etc.



I hope I have explain exactly what I am trying to achieve.



Thanks for any help

Mel
 
Here you go:

=IF((L30="D")*(($P$53+$Q$53)>199),0.1*(P30+Q30),IF((L30="N")*(($P$53+$Q$53)>499),0.1*(P30+Q30),0))

It puts a 0 in the cell if the value in column L is not a "N" or a
"D".

It puts a 0 in the cell if the value in column L is a "D" and the
result of $P$53+$Q$53 is 199 or less.

It puts a 0 in the cell if the value in column L is an "N" and the
result of $P$53+$Q$53 is 499 or less.

If it doesn't put a zero in the cell, it puts 10% of the sum of
Columns P and Q.

Is that what you wanted?
 
If you want to do it with the And/Or construct, it would look like
this:

=IF(OR(AND(SUM($P$53:$Q$53)>449,L30="N"),AND(SUM($P$53:$Q$53)>199,L30="D")),SUM(P30:Q30)/10,"")

Note that your version didn't put a zero in the cell if it didn't
satisfy the criteria, it puts a single space.
 
Thank you so much Dranon.


dranon said:
If you want to do it with the And/Or construct, it would look like
this:

=IF(OR(AND(SUM($P$53:$Q$53)>449,L30="N"),AND(SUM($P$53:$Q$53)>199,L30="D")),SUM(P30:Q30)/10,"")

Note that your version didn't put a zero in the cell if it didn't
satisfy the criteria, it puts a single space.
Click to expand...
 
Back
Top