Function needed????

[prbucci]

I need a function that will help me add to any cell tha
doesn't reach the disired amount.

If the required number is 14 and a calculated cell comes up with 13 o
less; I need a funtion to take that number and add a penalty number t
it.

For example: For arguements sake the penalty is $150.00. Suppose cel
1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I nee
cell 1D to realize that the number in 1C did not equate to atleast 1
and therfore 1D should show $150.00 and for every cell selected tha
falls short of 14 should add an additional $150.00 to 1D

 

[Guest]

i dont quite understand "and for every cell selected that
falls short of 14 should add an additional $150.00 to 1D.",but if you type
in 1d
=if(1c<14,150,0) .Format your 1d for a currency or accounting format if you
really want the $ sign to show

 

[prbucci]

I have a colum of 20 cells and every one of them need to be checked to
see if they meet the required amount and if not they need to be
reported to one cell and tabulated at 150 each. the $ does nt matter

 

[Guest]

so a range of twenty rows x2 columns need to "report" back to one cell to
give a multiple of 150 or 0? or is each row treated seperately like your
example and then all added up

 

[prbucci]

Thanks your suggestion works!

One problem I didn't for see was that in the row there might not b
anything entered into a cell and therfore that cell will report th
penalty.

I now need it to say less then 14 but more the -0- In other words
thru 13 should be penalized and no other number including -0

 

[prbucci]

Sorry I didnt pay attention to the last post. My reply to your questio
is; in the row each cell needs to add up to 14 or more indivdually no
the total row.

Back to my last reply though, it needs to be less then 14 but more the
-0

 

[Guest]

if i have understood correctly for each row(this for row 1 and copy down)
=IF(AND(A1>=1,A1<14),150,0)+IF(AND(B1>=1,B1<14),150,0)

 

[Biff]

Why not just count the the cells that are >0 <14 and multiply by 150?

=SUMPRODUCT((A1:B20>0)*(A1:B20<14))*150

Biff

 

[prbucci]

Thank You!!!!!!!!!!!

It was a success

 

[Guest]

very elegant biff