| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function needed????
 I need a function that will help me add to any cell thatdoesn'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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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: I need a function that will help me add to any cell thatdoesn'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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| DAverage Function help needed | Excel Worksheet Functions | |||
| Desperate Help needed with a function. | Excel Worksheet Functions | |||
| function needed | Excel Worksheet Functions | |||
| IF function help needed | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
