Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help Again-Condition Formula
Can I please get additional help with the post that I posted earlier.
My first question was If G37=daily, then F37 cannot be greater then 1. If it is greater then one, then an error message will show in K37. I received a correct answer for that question.. =IF(AND(G37="Daily",F37<=1),"OK",Error") I didn't think that some items will be based on hourly rates and I will not know what rolls will be that until the look up code picks up if it's a daily or hourly item that I'm billing. (I have a billing codes that are based on different rates that are picked up on another sheet, based on the code number I use) Can the formula have that if it's daily, that it will show the error if over 1, and if hourly it will not show an error if it's over 1? Lordy mercy, I'm having a hard time even saying what I need it to show....I hope someone will understand what I'm needing and respond.... Instead of lost in excel, I'm going to say dumb in excel..... I would appreciate the help.... Thanks again. Kareinia |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help Again-Condition Formula
I'm not sure if I have understood your question completely or not. Does this
do what you are looking for? =IF(AND(G37="Daily",F371),"Error","OK") Rick "Kareinia" wrote in message ... Can I please get additional help with the post that I posted earlier. My first question was If G37=daily, then F37 cannot be greater then 1. If it is greater then one, then an error message will show in K37. I received a correct answer for that question.. =IF(AND(G37="Daily",F37<=1),"OK",Error") I didn't think that some items will be based on hourly rates and I will not know what rolls will be that until the look up code picks up if it's a daily or hourly item that I'm billing. (I have a billing codes that are based on different rates that are picked up on another sheet, based on the code number I use) Can the formula have that if it's daily, that it will show the error if over 1, and if hourly it will not show an error if it's over 1? Lordy mercy, I'm having a hard time even saying what I need it to show....I hope someone will understand what I'm needing and respond.... Instead of lost in excel, I'm going to say dumb in excel..... I would appreciate the help.... Thanks again. Kareinia |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help Again-Condition Formula
That would work if Daily would only come up on certain rolls...But if I put
in a charge code that will have an hourly rate, I would need that roll to be okay... It looks like this on my spread sheet F37 G37 K37 classification #of Hours/Day Hours or day Show Error Dozer 10 Hours Air Compressor 1 Day I need it to show that I made a mistake if I put more then 1 in the #of hours/day column, if the classificate is charge by daily rate. I catch errors where I am keying that I put a 10 in where it should be 1 for a daily rate... I need to copy the condition down for several rolls that will change from either an item that is based on hourly or items that will be based on daily. If I put 10 on a classification that should be daily, I actually bill for 10 days...I was wanting a flag that would come up if I made that mistake. I appreciate you helping me....I've tried to come up with it myself, but no luck.... "Rick Rothstein (MVP - VB)" wrote: I'm not sure if I have understood your question completely or not. Does this do what you are looking for? =IF(AND(G37="Daily",F371),"Error","OK") Rick "Kareinia" wrote in message ... Can I please get additional help with the post that I posted earlier. My first question was If G37=daily, then F37 cannot be greater then 1. If it is greater then one, then an error message will show in K37. I received a correct answer for that question.. =IF(AND(G37="Daily",F37<=1),"OK",Error") I didn't think that some items will be based on hourly rates and I will not know what rolls will be that until the look up code picks up if it's a daily or hourly item that I'm billing. (I have a billing codes that are based on different rates that are picked up on another sheet, based on the code number I use) Can the formula have that if it's daily, that it will show the error if over 1, and if hourly it will not show an error if it's over 1? Lordy mercy, I'm having a hard time even saying what I need it to show....I hope someone will understand what I'm needing and respond.... Instead of lost in excel, I'm going to say dumb in excel..... I would appreciate the help.... Thanks again. Kareinia |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help Again-Condition Formula
Given the indicated purpose behind your question, perhaps you might consider
this alternative approach. First, though, I need to say your posted sample layout is a little confusing... you seem to be referencing a specific row (37), but yet you appear to have multiple rows of data (as would be expected), plus your spacing for these column designation are spaced oddly on my newsreader. So, assuming your data actually starts at Row 2 (assuming Row 1 is a header row) and that your "# of Hours/Day" column is Column G and that your "Hours or day" column is Column K, do the following... 1) As long as the header text in K1 will not be the single word "Day" (or "Daily" depending on which term you are actually using in that column when not Hours), then select the entire Column G. 2) Click Format/Conditional Formatting on Excel's menu bar. 3) Select "Formula Is" from the first drop-down box in the dialog box that appears. 4) Put this formula in the second field (next to the Formula Is drop-down)... =AND(G11,H1="Day") Note: Use "Daily" instead of "Day" if that is what you put in Column K 5) Click the Format button and then click the Patterns tab on the dialog panel that appears. Select a pale color from the grid of colors (I would suggest the pale red) and then OK your way back to the worksheet. From now on, if "Day" (or "Daily", again, depending on what it is you actually enter in Column K) appears in Column K and you put an entry greater than 1 in Column G... that "greater than one" number will be flagged in the color you picked in Step 5 alerting you to the error visually. Rick "Kareinia" wrote in message ... That would work if Daily would only come up on certain rolls...But if I put in a charge code that will have an hourly rate, I would need that roll to be okay... It looks like this on my spread sheet F37 G37 K37 classification #of Hours/Day Hours or day Show Error Dozer 10 Hours Air Compressor 1 Day I need it to show that I made a mistake if I put more then 1 in the #of hours/day column, if the classificate is charge by daily rate. I catch errors where I am keying that I put a 10 in where it should be 1 for a daily rate... I need to copy the condition down for several rolls that will change from either an item that is based on hourly or items that will be based on daily. If I put 10 on a classification that should be daily, I actually bill for 10 days...I was wanting a flag that would come up if I made that mistake. I appreciate you helping me....I've tried to come up with it myself, but no luck.... "Rick Rothstein (MVP - VB)" wrote: I'm not sure if I have understood your question completely or not. Does this do what you are looking for? =IF(AND(G37="Daily",F371),"Error","OK") Rick "Kareinia" wrote in message ... Can I please get additional help with the post that I posted earlier. My first question was If G37=daily, then F37 cannot be greater then 1. If it is greater then one, then an error message will show in K37. I received a correct answer for that question.. =IF(AND(G37="Daily",F37<=1),"OK",Error") I didn't think that some items will be based on hourly rates and I will not know what rolls will be that until the look up code picks up if it's a daily or hourly item that I'm billing. (I have a billing codes that are based on different rates that are picked up on another sheet, based on the code number I use) Can the formula have that if it's daily, that it will show the error if over 1, and if hourly it will not show an error if it's over 1? Lordy mercy, I'm having a hard time even saying what I need it to show....I hope someone will understand what I'm needing and respond.... Instead of lost in excel, I'm going to say dumb in excel..... I would appreciate the help.... Thanks again. Kareinia |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help Again-Condition Formula
4) Put this formula in the second field (next to the Formula Is
drop-down)... =AND(G11,H1="Day") Note: Use "Daily" instead of "Day" if that is what you put in Column K Damn! I forgot to change the column reference from my test case... the above formula should have been this... =AND(G11,K1="Day") Sorry for any confusion that may have caused. Rick |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help Again-Condition Formula
Thanks for you help!
"Rick Rothstein (MVP - VB)" wrote: 4) Put this formula in the second field (next to the Formula Is drop-down)... =AND(G11,H1="Day") Note: Use "Daily" instead of "Day" if that is what you put in Column K Damn! I forgot to change the column reference from my test case... the above formula should have been this... =AND(G11,K1="Day") Sorry for any confusion that may have caused. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Condition to Formula | Excel Discussion (Misc queries) | |||
MORE THAN ONE CONDITION IN A FORMULA | Excel Worksheet Functions | |||
Formula For If & Or Condition | Excel Discussion (Misc queries) | |||
If formula and #N/A as a condition | Excel Discussion (Misc queries) | |||
HELP? ...a second condition in one formula | Excel Worksheet Functions |