I am a heavy excel user and occassionally I will run into issues where my formulas are not recognized. When I type in the formula, the text string that I entered is returned. This does not happen all the time but definitely enough to frustrate. Assuming you haven't formgotten an = sign, this only happens when the cell you are entering data into is formatted as text. If you insert a column to the right of another column, then it will take on the formatting of the column to its left, so if text to the left thne your new column is text. Just format the cell(s) as general, hit F2 and ...

I want to be able number columns across the top of my excel sheet. EX. (0, 0) (1, 0) (2, 0) How do I get it to keep adding 1 number to the front # and keep the second # as a zero? When I do it down a column it works fine but when I try to do it across it just keeps repeating itself so that I get: (0, 0) (1, 0) (2, 0) (0, 0) (1, 0) (2, 0) instead of (0, 0) (1, 0) (2, 0) (3, 0) (4, 0) (5, 0) Any help would be greatly appreciated. Enter in A1 ="(" & COLUMN()-1 & ", 0)" returns (0, 0) (1, 0) (2, 0) (3, 0) (4, 0) (5, 0) as it is copied across row 1 Gord Dibben ...

I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

Can someone tell me the difference between these two formulas? Thanks! Amy The primary difference is that DSUM requires that you use a range of cells as criteria, and that both the data range and criteria range have column header labels. SUMIF does not require either of these. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "cvgairport" <cvgairport@discussions.microsoft.com> wrote in message news:8F08EDD4-B26D-42EB-A2A6-2BEA2C153B4F@microsoft.com... > Can someone tell me the difference between these two formula...

Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Is there a formula to obtain a total for each quarter which excludes including the year? Thus avoiding having to change to year on a yearly basis. Your help would be appreciated. -- Carol First hit on web search: http://www.exceltip.com/st/Calculating_the_Quarter_Number_for_Calendar_and_Fiscal_Year/932.html quarter based on calendar year (with date in A2) =INT((MONTH(A2)-1)/3)+1 quarter based on FY starting in September, again w/date in A2: =MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1 "Caroline" wrote: > Is there a formula to obtain a total for each quarter whic...

I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I use two functions in a formula? I used the IF function and am using a percent, however, I need it to also round the number down, because other cells use it and latter I am always one cent off. example: =IF(SUM(J3)>0,SUM(J3*25%),0) So if the answer is 25.49666, I want it to calculate to 25.49. Thanks, =IF(J3>0,ROUNDDOWN(J3*25%,2),0) -- HTH RP (remove nothere from the email address if mailing direct) "buddyorliz" <buddyorliz@discussions.microsoft.com> wrote in message news:A584EFD4-11BE-41AF-8F06-585059782F27@microsoft.com... > How do I use two funct...

I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Apparently I've exceeded the max number of characters. Is there an override? Hi no way around this limitation. But you may post your formula as there may be better ways to achieve your goal. e.g. - use defined names within your formula - etc. -- Regards Frank Kabel Frankfurt, Germany "rkowaluk" <rkowaluk@discussions.microsoft.com> schrieb im Newsbeitrag news:8CB4DEE8-F709-4FDC-9695-1252EC95F1C5@microsoft.com... > Apparently I've exceeded the max number of characters. Is there an override? Use multiple cells. If you refer to cells on other worksheets, use shorte...

I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

Does anyone know how to build a formula that rounds numbers to the nearest $.50? For example: If the number was $42.26 it would round to $42.50, but if it were $42.24 it would round to $42.00. Denise, =ROUND(C7*1/50,2)*50 Or if you have the analysis toolpack installed (Tools -> Add-ins) =MROUND(C7,0.5) Dan E "Denise" <dhill@pressdemocrat.com> wrote in message news:1e00601c38908$9ed72770$a601280a@phx.gbl... > Does anyone know how to build a formula that rounds > numbers to the nearest $.50? For example: If the number > was $42.26 it would round to $42.50, bu...

There is a "mystery" column in an existing worksheet that I can enter numbers into but they do not show in the cell while I them. And they don't show in the cell after they are entered. But - they show in the formula bar as they are being keyed and aftewards. The cells seem to contain the data even though nothing appears in the cell - the value is correctly included in a sum'd cell along with visable values from adjacent cells in adjacent columns. Also - if i key the numberinto a cell in a different column, I see it in the cell as it is being typed and aftward. The...

I have $700,000 to spend on advertising for three different products. My market share for the three products is 51.96%, 36.07% and 41.25%. I want to apportion my 700K between the three products so that the product with the lowest market share gets the most advertising dollars, etc. Using my data, I've been trying to devise a formula all night that would do this, but I'm stumped. I could use a hint... Here's one way of doing it - put 700,000 in A1 and put your 2 percentage shares in B1:B3 and then put this formula in C1: =3DA$1*(1-B1/SUM(B$1:B$3))/2 Format as currency and co...

Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

Hi, i have excel sheet its contain lot of formula and VBA(macro) code, its for used for user purpose, i need how to protect the formula and VBA at the time of user using. kindly let me know , kindly help me out i need user password : for user can upload the data in non restriction cell, admin password : can change any thing(change power) pls help me, Please ask on the Excel board. "deen" wrote: > Hi, > > i have excel sheet its contain lot of formula and VBA(macro) code, > > its for used for user purpose, > > i need how to protect the formula and ...

I need a tiebreaker for a formula. Right now the formula reads: =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when there is a tie it shows on one store twice instead of listing the two separate stores. I have two stores have the exact scores and it list the store with the lower store number twice. For example I have store 598 and store 698 both with a score of 100% but in the ranking of the stores it shows store 598 twice instead of 598 then 698. Is it possible to have a tiebreaker, with the index formula, that can list the stores in descending order, 598 first...

Can anybody explain this? I have a spreadsheet with a column of 18 numbers. In the 19th row, I have a formula to sum the first nine cells. As I enter numbers in row 1 down to row 10 and below, the formula magically changes to include the additional rows! In case it's of relevance, the cells have conditional formatting to colour background depending on value. Otherwise, nothing special about the cells at all. I am mystified! This could be a useful function if I knew how to create it again!! Here's what it says in help... Extend formats and formulas to additional rows By default, Mic...

Does anyone know how to change the date format in excel to read (ie 20020429 instead of 04/29/2002..... Is there a formula or something I tried changing the date format and it doesn't have the format need.... Please hel -- Message posted from http://www.ExcelForum.com Right-click the cell you want to change, select the Format Cell dialo box , then the Number tab, then Custom, enter yyyymmdd then push OK. HT -- Message posted from http://www.ExcelForum.com >-----Original Message----- >Does anyone know how to change the date format in excel to read (ie) >20020429 ins...

hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...