I have and excel spreadsheet which I wish to record an error message if one cell as a value of more than zero and the cell below it has a value of zero. I have managed the first part of the formula but can not figure out how if the both cells are zero to return a " " result.

0 |

4/12/2010 8:45:01 PM

"Janie" wrote: > I have and excel spreadsheet which I wish to > record an error message if one cell as a value > of more than zero and the cell below it has a > value of zero. Ostensibly: =if(and(A1>0,A2=0), "ERROR", "") ----- original message ----- "Janie" wrote: > I have and excel spreadsheet which I wish to record an error message if one > cell as a value of more than zero and the cell below it has a value of zero. > > I have managed the first part of the formula but can not figure out how if > the both cells are zero to return a " " result.

0 |

4/12/2010 9:05:01 PM

=IF(AND(A1>0,A2=0),"ERROR","") -- Regards Dave Hawley www.ozgrid.com "Janie" <Janie@discussions.microsoft.com> wrote in message news:C7EFBAA7-4211-44C3-8545-AFC2193D6002@microsoft.com... >I have and excel spreadsheet which I wish to record an error message if one > cell as a value of more than zero and the cell below it has a value of > zero. > > I have managed the first part of the formula but can not figure out how if > the both cells are zero to return a " " result.

0 |

4/13/2010 4:40:09 AM

This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C61A25.29EC1E10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I didn't receive a response on my last post so I worked on my problem = some and came up with some results. Could someone look over my formulas = and tell me if I can achieve this same result any easier? Here is a sample of the worksheet: D E F G H I J K L M N O=20 22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total=20 23 off 12 11 11 11 11 off 40 15 1 10.00 $645.00=20 ...

I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

I posted this a couple of days ago but doesnt seem to have appeared so I hope this is not a duplicate. Original formula from previous post... =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*24 I have changed the formula to =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*C13 As a test I have put in aircon running for 1 hour between 10-11 am So this should return a value of .30 It is ...

I have created a workbook with several sheets. The first sheet consists of a master list of 8000 numbers. The second sheet is where I paste a smaller list of numbers. The workbook performs a search and returns numbers that match on the third sheet. The third sheet also has formulas. I want to be able to copy and paste the resulting info on the third sheet to another workbook without copying any of the formulas......I just want the info. Any suggestions?? Ken Copy>Paste Special>Values>OK>Esc. Gord Dibben Excel MVP On Thu, 18 Nov 2004 14:21:13 -0800, "Ken"...

Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

I have a spreadsheet with a filename in a cell. I would like to reference that filename in a formula. I can't seem to get it to work. "+cell number" doesn't work. Any suggestions? You would normally use INDIRECT to do this, along the lines of: =INDIRECT("["&A1&"]Sheet1!C2") where A1 contains your filename (with the .xls extension) and you are trying to return data from C2 on Sheet1 of that file. However, INDIRECT will only work with files that are open, so you would have to have the file open for this to work. Hope this helps. Pete On...

{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1, 2,3})*{42,21,11})} This formula works fine, up until the separated part, then it just does nothing(I put the spaces in just to clarify the problem area for this discussion). No matter what order put the arguments in, it only works up to the same point. Is this just a case of to much info? How do I extend this formula (I need it to be even longer the above example). thanks. Hi Atom, Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER since it is not an array formula Bernard "atom" <hmm@hmm.com...

Am i able to create a formula that gives me monetray totals for expenditure on hotels, split into totals for 6 varying business sectors? ...

Would like to use a benefit or deduction based on a formula to calculate the amount instead of choosing only a flat amount or % as it is currently set up in GP version 9. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Commun...

HI. How do you change a formula to read the value as a result of a formula and not necessarily as a real number? Example: In G6, i have the following formula that gives me the hour of a time in F6 =IF(F6>0,MOD(F6-"1:00",1),"") F6 contains 00:30:08, The result is 23 In another cell, I am using this formula, but it doesnt recognize the 23. =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23")) The 23 is actually the HOUR of time so it isn't a whole number or real number persay. It is a rounded time to the hour. Yes, but it isn't a text ...

is there a way i can protect the formulas without protecting and passwording and identifying various ranges? the user accidentally enters data into a calculated cell and wipes out the formula. thank you, mike g Mike, Set the cells that the user can change to unlocked using Format, Cells, Protection, set those they can't to locked. Protect the Sheet. Robin Hammond www.enhanceddatasystems.com "work" <mike@radiant.net> wrote in message news:42140299.10B3@radiant.net... > is there a way i can protect the formulas without protecting and > passwording and identifying ...

Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried messing around with IF statements but I cant seem to figure it out =IF(ISERROR(cellref),erroraction,noerroraction) =IF(ISNUMBER(cellref),noerroraction,erroraction) You can also use it in range functions, such as =SUM(IF(ISNUMBER(range),range)) but you must array enter (Ctrl-Shift-Enter) Jerry DLZ217 wrote: > Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried > messing around with IF statements but I cant seem to figure it out "Jerry W. Lewis" <post_a_reply@no_e-ma...

I know this is going to be simple but everything I've not got anything to work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0" in I11 if there aren't any values anywhere in I2 to I10? Thanks. :oD If there are no values to sum the formula should already be returning 0. What result are you getting? Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:OV$7dJLnHHA.3704@TK2MSFTNGP02.phx.gbl... >I know this is going to be simple but everything I've not got anything to >work. How can we change f...

I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 You ...

I have a spreadsheet that lists all the types of machines and thei model numbers. When a customer places an order for a specific machine I need to reference the other workbook in order to get the model number Is there a way to have this done automatically. For example: when enter a machine type (CE-10), I want the model number for that machin to be entered automatically in another cell, without having to open th other workbook and copy and paste the number myself. I considered dat validation (using a list) but can't reference another workbook. Any thoughts? Thanks, De -- da ------...

I have a workbook with several worksheets in it. The sheets are not related to each other except that I used the copy worksheet feature to add sheets. There is a formula cell which totals the two cells to the immediate left on each worksheet. For some reason, when I change one of the values in the cells being added, it doesn't update the total, unless I retype the formula in the cell. I have had this problem one other time in the past. I'm not sure how to fix it. -- Sincerely, Beverly76 Hi you may check if automatic calculation is enabled ('Tools - Options - Calculate...

Hello NG, the following sheet: - In column A are words in German. - In column B are the translated words in English. - In C1 is one English clause with only one single German word in it. ToDo: I want translate this word in English and write it o D1. But I dont want to use VBA or more then one cell for the calculation. Such solutions I can make for myself. I'm working since several years with Excel and VBA. Currently I'm playing with this matrix formula. (I have translated the Excel function names from German to English, but I dont know if I used the right words.) {=IF(ISERROR(SEARCH...

A spreadsheet containing two colums. Column A has dollar amount entries. Column B has date entries. These entries are entered throughout the month and every month of the year. I need a formula that will sum the amounts in column a only if they are during the month of march from column b (for example) "mpiton" <mpiton@discussions.microsoft.com> wrote in message news:F1965B8B-EF82-41A6-89CF-86FF72706DB2@microsoft.com... > A spreadsheet containing two colums. Column A has dollar amount entries. > Column B has date entries. These entries are entered throughout th...

I've come up against a problem in Excel, I need help to figure out a formula. A B C D 1 Number Of Months 24 �60.00 �1,440.00 2 Number Of Weeks 96 �15.00 �1,440.00 I will try to make this clear to understand, what I need is when I change cell C2 either adding more money or taking away, I need cell B2 to reflect the change either increasing the amount of weeks needed to get to D2s total or decrease depending on what was changed in C2. Any advice or help would be much appreciated. On Oct 10,...

I have an Excel sheet that shows hourly rates based on the number of hours a customer purchases. Example Column A Column B Column C (Hours)From: (Hours)To: Hourly Rate 1 40 $35 41 80 $32 81 120 $30 I have a seperate column under a different tab where a sales rep enters the total number of hours. I would like the formula to automatically lookup the chart above and show the appopriate hourly rate. Thanks, Donne Hi - best to ask the Excel gurus in an Excel n...

Can anyone help with the protection of part of an Excel worksheet (Excel 2003)whilst still allowing sorting and filtering of data? Individual users paste data into the worksheet and, despite comprehensive instructions, sometimes manage to delete / overwrite existing formulas. The individual users need to sort and filter the whole worksheet after their "input" so the basic procedure of locking and protecting the cells containing the formulas isn't an option. Any help would be most welcome. If users input data to w/sheets they do not need access to cells containing ...

I need to enter one date, and have it compute the future date based on the number of years: For Example: - Date Added: June 2007 - Number Of Years: 5 Year - Expiration Date (Need Formula): June 2012 How would I get that result. BTW: Using Excel 2000 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) -- Kind regards, Niek Otten Microsoft MVP - Excel "S" <S@discussions.microsoft.com> wrote in message news:8504259F-4193-4CE3-9387-EAB68624076E@microsoft.com... |I need to enter one date, and have it compute the future date based on the | number of years: | | For Example: | - Date Added: J...

Does anyone know how to search for info from multiple Excel worksheets using the VLOOOKUP formula or any other formula. I have my VLOOKUP formula(column B) and list of values(column A) (sorted in ascending order) on Worksheet 7 and I need to search for these values in Worksheets 1,2,3,4,5& 6 Column F. Is this possible with Excel ? I've tried this by using the formula VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE) but I get a #VALUE! error. You will need a construct along these lines: =IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2...

Hi, very new to this group (just signed up) and somewhat new to Excel. I am trying to see if there is a way to set up a variable for the column in a formula cell so that you can change the letter in one cell and it will change all of the formulas in the workbook that referenced the old lettered colum to the new lettered column. Example... I have a book that has Jan and Feb in column A and Column B respectively. In A2 I have 100 and in B2 I have 200. If I have a reference to A2 in cell C1 it should return a value of 100. What I am looking to do is in NEW cell d1, have a feild where I can t...

I tried to post a question earlier but it never appeared on the messag list. Not sure if it went through. So here it is again. I am trying to set up a formula where when I type in a weight i another column it will automatically puts in an assigned value for tha weight. The problem is that the weights range from 110 through 260. have found that I cannot put in more than 7 IF functions in a formula. This is my first time trying to do something like this and I am havin problems. I know there has to be a way to do this. For example I tried this formula and it worked except I con only put ...