I need help with a formula

[ken McIntyre]

I am trying to write a formula, that will look at a range of cells (5 of
in total) and if the number in any of those cells is between 600 and
1200 then I want to enter an amount from another cell

in lay mans terms

if cells A5 to E5 are greater than 600 and less than 1200 then enter A12
or enter zero, where A12 would be an amount in dollars

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

One way:

=IF(SUMPRODUCT(--(A1:A5>600),--(A1:A5<1200)),A12,0)

(change those inequalities to >= or <= if you need to.)

 

[Ken McIntyre]

Dave

Thanks for the response, please confirm that that formula will not look
at the sum of the five cells, as I want it to look at the value in each
cell individually

many Thanks

ken

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

What happened when you tried it?

Put some test data in there and see if it breaks when you change those values.

 

[jim]

please try this array formula: =IF(AND(A5:E5>600,A5:E5<1200),A12,0), then
enter "CTRL+SHIFT+ENTER" .