If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
conditional formatting
I've read most of the similar topic posts but still haven't quite figured out
what to do. I used to teach computer science so I know conditionals, I just don't know how to put it in Excel ( we're using 2007). What I have is a workbook set up for time keeping. If someone enters a P in any cell I need it to Add 1 to the AL column of that row, but if they enter a lower case p it needs to add only 0.5 to the AL column of the row. For the same cell, if the value entered was X or x, the AK column of the row is updated by those same values. Thanks for any responses! |
#2
|
|||
|
|||
conditional formatting
If someone enters a P in any cell
Well, you need to narrow it down from "any cell". An Excel 2007 worksheet has billions of cells! Try something like this: For "P" : =SUMPRODUCT(--(EXACT(A11,"P"))) For "p" : =SUMPRODUCT(--(EXACT(A11,"p")))/2 Follow the same logic for X and x. -- Biff Microsoft Excel MVP "dkstech" wrote in message ... I've read most of the similar topic posts but still haven't quite figured out what to do. I used to teach computer science so I know conditionals, I just don't know how to put it in Excel ( we're using 2007). What I have is a workbook set up for time keeping. If someone enters a P in any cell I need it to Add 1 to the AL column of that row, but if they enter a lower case p it needs to add only 0.5 to the AL column of the row. For the same cell, if the value entered was X or x, the AK column of the row is updated by those same values. Thanks for any responses! |
#3
|
|||
|
|||
conditional formatting
Thank you. The reason I said "any cell" is that there are 242 possible working days in our year, plus we can work on weekends sometimes as well - so the range of cells that are used are actually in a matrix, not an array and therefore didn't know how to really say that before. Thank you for your reply - I'll give that a shot. "T. Valko" wrote: If someone enters a P in any cell Well, you need to narrow it down from "any cell". An Excel 2007 worksheet has billions of cells! Try something like this: For "P" : =SUMPRODUCT(--(EXACT(A11,"P"))) For "p" : =SUMPRODUCT(--(EXACT(A11,"p")))/2 Follow the same logic for X and x. -- Biff Microsoft Excel MVP "dkstech" wrote in message ... I've read most of the similar topic posts but still haven't quite figured out what to do. I used to teach computer science so I know conditionals, I just don't know how to put it in Excel ( we're using 2007). What I have is a workbook set up for time keeping. If someone enters a P in any cell I need it to Add 1 to the AL column of that row, but if they enter a lower case p it needs to add only 0.5 to the AL column of the row. For the same cell, if the value entered was X or x, the AK column of the row is updated by those same values. Thanks for any responses! |
#4
|
|||
|
|||
conditional formatting
OK, it'll work on a matrix just as well:
=SUMPRODUCT(--(EXACT(A15,"P"))) =SUMPRODUCT(--(EXACT(A15,"p")))/2 -- Biff Microsoft Excel MVP "dkstech" wrote in message ... Thank you. The reason I said "any cell" is that there are 242 possible working days in our year, plus we can work on weekends sometimes as well - so the range of cells that are used are actually in a matrix, not an array and therefore didn't know how to really say that before. Thank you for your reply - I'll give that a shot. "T. Valko" wrote: If someone enters a P in any cell Well, you need to narrow it down from "any cell". An Excel 2007 worksheet has billions of cells! Try something like this: For "P" : =SUMPRODUCT(--(EXACT(A11,"P"))) For "p" : =SUMPRODUCT(--(EXACT(A11,"p")))/2 Follow the same logic for X and x. -- Biff Microsoft Excel MVP "dkstech" wrote in message ... I've read most of the similar topic posts but still haven't quite figured out what to do. I used to teach computer science so I know conditionals, I just don't know how to put it in Excel ( we're using 2007). What I have is a workbook set up for time keeping. If someone enters a P in any cell I need it to Add 1 to the AL column of that row, but if they enter a lower case p it needs to add only 0.5 to the AL column of the row. For the same cell, if the value entered was X or x, the AK column of the row is updated by those same values. Thanks for any responses! |
Thread Tools | |
Display Modes | |
|
|