Ranges within and IF statement

**DON_BLACK** · 04-17-2013, 09:46 AM

I am trying to create a if statement that when enter a number lower than "0" it returns a value of "0" and anything higher than zero returns "50"

(J12)

=IF(J15<=0,"0","50") but I want it have a range so when data is enter in any cell in the "J" column this will update. Test file.xlsx

I want this.
=IF(J15:J67<=0,"0","50") just not writing the correct syntax

please review document

**Pete_UK** · 04-17-2013, 10:30 AM

Do you mean something like this:

=IF(MAX(J15:J67)<=0,0,50)

so that if any number in that range is greater than 0 the formula will return 50 ?

Hope this helps.

Pete

**DON_BLACK** · 04-17-2013, 10:41 AM

Ok, that works now in cell L15 (=(J15*(J$12/100)+(K15*(K$12/100))))

If a value is not given for either J$12 or K$12 then it shall default to whatever value is entered in J15 or K15. just in case someone does not enter anything in one of the cells it wont (process the formula) instead it will just default to that given value instead....

**DON_BLACK** · 04-17-2013, 10:47 AM

for instance if I enter "10" in cell J15, but 0 in K15 the formula in L15 is going to compute the value given in J15 and return an answer of 5 whereas. I want it not to compute unless a value above 0 in K15 is given resulting in computed value. If I am saying that correctly.

the value that should be returned is "10" when nothing is computed when there is no given number in K15 but there is a number in J15 and vice versa.

**Pete_UK** · 04-17-2013, 11:19 AM

Try this:

=IF(OR(K15="",J15=""),10,J15*J$12/100+K15*K$12/100)

which will trap things if either of the cells are empty. If zero is also an invalid entry, then you could use this:

=IF(OR(K15="",J15="",K15=0,J15=0),10,J15*J$12/100+K15*K$12/100)

Hope this helps.

Pete

**DON_BLACK** · 04-17-2013, 11:29 AM

it still does the same thing. If I use =IF(OR(K15="",J15=""),10,J15*J$12/100+K15*K$12/100) in cell L15 it returns half the value entered rather than the number entered when K15 is "0"

this formula does not work at all
=IF(OR(K15="",J15="",K15=0,J15=0),10,J15*J$12/100+K15*K$12/100)