If the numeric value in a given cell falls within a predefined range (500 to 1000), I want the value to be multiplied by a constant value (0.05). If the criteria is not met, I want the cell identified as the recipient of this action to reflect a value of 0.00.

Would someone please show me what this formula should look like? Certainly would appreciate it.

Immediate solution:
Assume your target cell is D1, then in E1 put this formula:

=IF(500<D1<1000,D1*0.05,0.00)

(to get the two digit display for the "false" portion, you will have to format the cell that way. Right click cell, choose Format Cell, Number (tab), then choose "Number in the left, and two decimals on the right side. Click OK.

Better Solution

It might also work better to put the following numbers into these cells and use the corresponding formula:

A1 = 500
B1= 1000
C1 = 0.05
D1 (target cell)
E1 (formula)

=IF(AND(D1>A1)*(D1<B1),D1*C1,0.00)

The reason for doing it this way is that if you reference these values in several formulas, you only have to change A1, B1, C1, rather than searching through every formula.

Thanks a ton....works great! I really appreciate
you taking the time to respond.

Dave

Glad that it worked, and happy to help.

Shades

this was great - thanks!
I didn't realize you could compare two values in one if statement in excel.

---------------------
A1 = 500
B1= 1000
C1 = 0.05
D1 (target cell)
E1 (formula)

=IF(AND(D1>A1)*(D1<B1),D1*C1,0.00)
---------------------

I have often done nested if statements to get this result.

I have also tried unsuccessfully to write it this way:
=IF((D1>A1) AND (D1<B1),D1*C1,0.00)

Excel 2000 Help doesn't seem to provide any info on syntax for combining such expressions instead of nesting if statements. Do you (or anyone) know any where to get info on excel syntax for this?


Thanks!

I bought the book MS Excel 2000 Formulas by John Walkenbach. One of the best investments I have made. You can pick up a used copy on Amazon for about $15.

700 pages of most types of formulas and combinations; plus expanding into array formulas. And he also shows what you might think "should" work, but doesn't and why. He also includes several chapters on writing UDF (User Defined Functions) through VBA.

You can add even more conditions.

=IF(AND(First test)*(Second Test)*(Third Test),.....)

You can also nest IF statements within IF statements (up to seven nested IF's - although there are work-arounds for even that).

And even more powerful formula is SUMPRODUCT, which can do things like summing with multiple conditions (SUMIF can handle only one condition), and even count items based on several criteria. But SUMPRODUCT will cost in terms of processing calculation time if there are many to do.

BTW, I am still learning this whole thing about Formulas and what can and cannot be done.

Thanks!