Function needed????

[prbucci]

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.

[paul]

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:

>
> 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
>
>

[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

[paul]

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
>
>

[prbucci]

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]

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-

[paul]

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)
--
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
>
>

[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

"paul" <[email protected]> wrote in message
news:[email protected]...
> 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)
> --
> 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
>>
>>

[prbucci]

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

It was a success

[paul]

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:B20>0)*(A1:B20<14))*150
>
> Biff
>
> "paul" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
> > --
> > 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
> >>
> >>
>
>
>