Is there a way to show what ROW contains the highest value for a range? Say I have numbers sequentially from 1 to 500 starting in row 50 so number 1 is in A50 and number 500 is in A550, is there a way to have it look through the entire range and return 550 (the row with the highest number in column A)?
View 6 RepliesI have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
i use function countif "aa" in range A2:F2 COUNTIF(A2:E2,"aa")how i can do, if i do not need 0 to show when it not found "aa". i want it show blank
View 4 Replies View RelatedI have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11:
=SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39:
=(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
i m working on a mechanical project , I need to do some modifications to my XLS file that i aint familair with.
http://aycu36.webshots.com/image/348...0572870_rs.jpg
I've made a chart that contains zero values. To prevent that the chart shows the zero values as 0% on the vertical axis, I've created a formula that shows zero values as NA.
So far so good, no disturbing 0% lines in my chart!
Now my issue:
Say cell A2 shows NA.
Cell B2 though has a formula that needs the data from A2. So it should se3e A2 as a 0 value and not as NA because B2 then also shows NA.
I don't know if this is possible, because I actually say: don't show 0 but use it as 0...
I have a cell in which numbers are displayed. The next cell is a calculation to display percentage. Lets say Cell A1 = 12,45. Cell B = 12%. What I would like is for cell C1 to sometimes display the "12,45" and other times the "12%". I imagine I have to get excel to see the % sign as text, but I do not know how.
View 2 Replies View RelatedI am using the following formula and getting Div# - but I would like to put something in the formula that says if it pulls Div#, instead show blank - does anyone know how to do this?
I know you can use IS error with V lookups & LEN - but not quite sure with this.
=F7/F12
I need to indicate if the figures for this week has gone up or down compared to last weeks figures.
So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.
Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.
I am trying to display the month only in text by looking to another cell that contains a date. So, if A1 contains '11/12/08', I want B1 to contain 'December'.
View 2 Replies View RelatedOne of my colleagues wants Excel only to show whole thousands, but I am not sure how to do this
For example, if the number is 78654.98, he only wants it to show 78.
Is this possible?
In one cell i have £92.00 to 2 decimal places. If i increase that to 4 decimal places it is £91.9998. I need this £92.00 to show as £91.99 (only as 2 decimal places not 4)but when i go back to 2 decimal places it shows as £92.00 again.
View 2 Replies View RelatedI have a movie list. column A is the number location i have my movie stored, and column b is the actual movie name. I backup my movies so that my kids wont destroy them. The list is around 1000. I have duplicate backups, i want to have a list of just the duplicate movie names and there locations.
View 12 Replies View RelatedI want to get only the amount in a cell that contains date, name, and amount sample:
in A1:A2
1970 Mario Gonzalez $700
1973 Mario Krupa $500
i want only the amount of Gonzalez to be display in b1 (or any cell beside that "Gonzalez" name), and if not Gonzalez then no text to be display.
I have used excel to create a set of timesheeets and a total page, on the total page there is a column that will show you any varience on your hours each month (I.e if you have worked overtime or under your hours)
I have used the formula =(worked+hours+sick)-(weekly hours*week in month)
This works fine the only problem is in shows a minus number until the hours for that month have been entered so I cant create a total column, is they any way to get these numbers to show 0 until the times are entered?
How can I get the Tab name to show up in a cell?
View 8 Replies View RelatedI have a date of birth in cell "B13". It's formated as xx/xx/xxxx. I'd like to copy this into another cell where I have annual holidays listed, but, I'd like to have it formatted as mm:dd - omit the year.
View 2 Replies View RelatedI have a lot of rows, where they belong together in groups, but these groups are not in order. for example. 3,14,21,45 rows belonging together and a group of rows 1,16,32,67 a second group.
My desire is to define a "button" that shows only one group at a time.
See picture : excel 2 UK.jpg
In your code the lines in red are not accepted in Excel 2007. Actually show up as RED color in the VBE. Have I lost something from your code (AFAIK, this is original text).
View 4 Replies View RelatedI know that to show a tab in VBA you can write worksheet("NameOfTheTab").select
However, it does not work when the tab is just graph.
I have a total amount of $52,000 and I want to show an equal amount in twelve
columns which represent 12 months from sept 2005-sept 2006. My calendar goes
for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the
correct columns.
From Row 3 down (until it ends) I want to hide the rows that do not have "YES" in column P.
View 5 Replies View RelatedSheet3 Â ABCD1Â Â Â Â 274917654Â 7654374927635Â Â 474917632Â Â 574327524Â Â 675247492Â Â 775247491Â Â 874917432Â Â 976320Â Â
1076350Â Â 1176540Â Â 1274910Â Â 1374920Â Â 1474910Â Â 1574320Â Â 1675240Â Â 1775240Â Â 1874910Â Â 1976320Â Â 2076350Â Â
Spreadsheet FormulasCellFormulaB2
=MAX(A2:A31)B3{
=MAX(IF($A$2:$A$31<B2,$A$2:$A$31))}B4{
=MAX(IF($A$2:$A$31<B3,$A$2:$A$31))}B5{
[Code]...
Formula Array:Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!
I want to ask that how can i remove zero from data validation list OR from column B...
I have 3 worksheets in a workbook. How can I have the view go to A2 and View that on each worksheet? I do a Range("A2").Select But, it still shows the window on another column - row.
View 2 Replies View RelatedI am looking for a formula to fill C2 with either the value of A2 or B2 based on which ever is populated.
Col a & b are lists
a
b
c
1
Monthly
Quarterly
Period
2
Jan
Jan
3
Jan-Mar
Jan-Mar
how do I show microseconds in Excel? See my sample:
1 ms
1.15741E-08
=1/24/60/60/1000
Begin date
1/1/2012 0:00:00.000
1-Jan-12
Begin + 1 ms
1/1/2012 0:00:00.001
=B2+B$1
[code]....
How can I get lines 4 and 5 to show me microseconds? It looks like internally Excel can do microseconds (it's just a number I guess) - line 6 clearly shows they're different.
I have the following problem:
I got a table which is used to calculate the financial rent ability of certain projects.
The years are in the columns, so for every year there is an entry for revenues, costs, etc.
The lifetime of the projects differs (sometimes 10 years, 20 years, etc.). So if it is chosen that the lifetime is 10 years, I would like the table to only show (and also use) only the first 10 columns (for 20 years the first 20 columns), etc.Is this possible and if so how?
Get second and third nearest dates to the selected date
What I'm looking for is this:
When a date is entered in F2, cell G2 will display the contents of M2
In M2 there's a formula as viewed in the post, essentially what is happening is the value in F2 is checked in column J and if the corresponding date is matched then in the cell next to it in column K a check is made, if the value there is greater than 0 then the date match is returned and shown in G2.
If the cell in column K contains a 0, the formula then checks to get the next highest date with a value greater than 0, first it checks for dates before the requested date and if they all have 0 as in the example then it will find the lowest date from the dates that fall after that which was requested.
What I would like to add now is in the yellow boxes the 2n'd & 3rd nearest date with a matching cell in column K thats >0 and usng the date in G2 as the criteria/starting date
I've tried various attempts using large, if and match and i'm just about goggle eyed now (possibly the Bud at work)
Sheet1 *FGHIJKLM1Requested DateDate OfferedConfirmed*DateSlotsNearest Available Slot202/05/20133-May-2013
**1-May-2013013-May-20133****2-May-201302*4****3-May-201333*5****4-May-20131**6****5-May-20130**7
****6-May-20130**8****7-May-20131**9****8-May-20132**10****9-May-20132**11****10-May-20132**12
****11-May-20132**13****12-May-20132**14****13-May-20131**15****14-May-20130**16****15-May-2013
[Code] ...........
I have a worksheet "Data" that gets filtered, i would like this worksheet to "show all" when i click on any other worksheets?
View 9 Replies View Related