Function needed????
 

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

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

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

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
--
paul
remove nospam for email addy!



"prbucci" wrote:


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

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

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

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
--
paul
remove nospam for email addy!



"prbucci" wrote:


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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

Thanks your suggestion works!

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

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

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

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

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),15 0,0)
--
paul
remove nospam for email addy!



"prbucci" wrote:


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

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

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

=SUMPRODUCT((A1:B200)*(A1:B20<14))*150

Biff

"paul" wrote in message
...
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),15 0,0)
--
paul
remove nospam for email addy!



"prbucci" wrote:


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

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile:
http://www.excelforum.com/member.php...o&userid=30428
View this thread:
http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

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

It was a success:)


--
prbucci
------------------------------------------------------------------------
prbucci's Profile: http://www.excelforum.com/member.php...o&userid=30428
View this thread: http://www.excelforum.com/showthread...hreadid=501425

Function needed????
 

very elegant biff
--
paul
remove nospam for email addy!



"Biff" wrote:

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

=SUMPRODUCT((A1:B200)*(A1:B20<14))*150

Biff

"paul" wrote in message
...
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),15 0,0)
--
paul
remove nospam for email addy!



"prbucci" wrote:


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

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


--
prbucci
------------------------------------------------------------------------
prbucci's Profile:
http://www.excelforum.com/member.php...o&userid=30428
View this thread:
http://www.excelforum.com/showthread...hreadid=501425