I have following numbers in Column A. I need formulas to get results shown in Column B and C.
ColumnA ColumnB ColumnC
Numbers Formula?? Formula??
200001 January Jan 00
200101 January Jan 01
200102 February Feb 01
200710 October Oct 07
200912 December Dec 09
I want to show a date like 18-12-2008. This wil normal work with =()TODAY-356
But when I output text with this formula it will only show numbers:
="date "&TODAY()-365&""
I want to subtract 1 year of today.
I tried to set my cell to the date format but this did not work.
I'm attempting to create a formula that will find the name associated with a value, and return that name on the same column as that value in a later equation.
OK OK to illustrate it a little better:
There are three people: Bill, Ted, and Andy. Each one is ranked in Points, so my table looks like this:
A B
1 Bill 10
2 Ted 20
3 Andy 30
Later on, I want to rank the individuals based on their score, using a formula. Right now, I can rank the scores based using LARGE(B1:B3,1), then LARGE(B1:B3,2), and lastly LARGE(B1:B3,3). That ranks the numbers in descending columns. However, I want the information to automatically populate the name associated with that particular point total. So, I want the system to know that B1 is Bill's score, and rank it, in descending order, later on in the spreadsheet, with Bill's name.
I am trying to put together a report tracking sheet, ie I have different test that take a different length of time to return to the office and I want to be able to see when a test should be back from the lab,ie:
A blood sample takes 1 day, a urine sample takes 3 days.
I need a formula that basically says that if the data in cell A1 = Blood, (and I have the test date in cell A2) then the data in cell A3 should says A1=Blood, A3=A2+1 to give me the test due date
I found the following formula in one of the other posts;
=IF(E5="BLOOD",G5+1,IF(E5="URINE",G5+3,0))
The problem that this will only work with two tests and I have more than two tests, I have fourteen and each takes a different length of time to process.
I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet.
Columns: Name, Size, Date
nmlclex06114/12/2013
nmlclap121012/12/2013
nmlclex0651/10/2013
nmlclex0619/10/2013
nmlclex0611/01/2014
nmlclap12124/10/2013
nmlclex06113/11/2013
nmlclap12118/12/2013
nmlclap1219/12/2013
nmlclap12130/12/2013
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I have attached a spreadsheet : Excel Question.xlsx
I would like to add up in "F9" all the numbers in Column F (F4:F7) which have the name Capital Contributed or Recallable Distribution in Column A and are up to the date of B1 in column B.
Also I would like that excel puts an "X" in column G as soon as it either says Capital Contributed or Recallable Distribution and the date is up to the date in F1 in order to have a macro? that hides everything which has no x.
There should also be a show macro to undo it and show everything .
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG1G2B4C4D4E4F4G4=
ABCDEFG104/30/07$107,777$0*$0$0$107,777209/06/07-$107,777$227,399*$98,962$91,165$309,7493???$28,046$170,458$0$86,434$18,243*4*$28,046$397,857*
$185,396$109,408$417,526Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am having a problem which I imagine will be fairly simple to resolve but I can't figure it out!
Cell A1 contains a Date eg (01/01/07)
In Cell A2 I want to write a formula that says: Insert 'Warranty' if the system date is within 365 days of the date in Cell A1.
I quickly put together an inventory form on our company server where employees can essentially just enter part numbers, quantities, and a few other things, rather than write everything out. So what might happen is, an employee puts together a list of 10 items with their respective quantities, but a few days later, another employee pulls up the spreadsheet and needs to add a few more of a part already listed. Protocol for the company is to do a separate line, rather than add it to the line where the item is already listed. What I am trying to do is, create a separate sheet within that workbook, that simply lists the part numbers, in order, with their total quantity. I have attached a basic spreadsheet showing what I would want, with a tab at the bottom labeled, "What I want."
View 4 Replies View RelatedAnother spreadsheet I use has a score within a text and i want to know how I can add up the scores easily.
In the example below I want to know If a Formula can return '9 out of 12'. I know this looks easy so why bother, but my sheet has far more rows/columns of data.
I thought of using Left and Right formula's to strip the numbers out.
Name
Score
A N Other
3 out of 4
A N Other
2 out of 4
A N Other
4 out of 4
is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.
View 7 Replies View RelatedWhat I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.
Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.
The formula I have got to work is
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),4,4,4,7,7,7,10,10,10,1,1,1,),1)
but this makes the quarter dates as APRIL, JULY AND OCTOBER - they need to be MARCH, JUNE, SEPTEMBER
when I change the formula to
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),3,3,3,6,6,6,9,9,9,1,1,1,),1)
The dates work BUT if the following test is undertaken on 01/03/12 the date in Column B does not change to 01/06/12. Why? How do I get it to work?
In the sheet I am working on I have some numbers that represent todays activity. These numbers are volatile and reset when the date advances. I have a column on another tab that has all the days of the month as F4:F33
The results I want will go in the G4:G33 column.
C2 is Today() to avoid too many Today()s.
Active!J10 is the value I need in the G column.
Basically what I need is the value from Active!J10 to be copied to whichever cell corresponds to the date next to it in the G4:G33 and then stay there when the date advances
The formula I am using for the g range is :
=IF(F24=C2,Active!J10)
=IF(F25=C2,Active!J10)
=IF(F26=C2,Active!J10)
And so on down the column.
I dont mind the FALSES for future dates, but the current one changes to false upon the advancement of the date. Basically, I need a running log of the final numbers going down the G range and for those values to stay there when the date advances.
I know I can use text to columns to do this. I have horse results listed in column A as follows 3.5L (i.e. 3.5 lengths), 4L and so on. Every number ends with an L. I want a formula in column O that just gives me the number as below:
3.5L 3.5 (in column O)
4.5L 4.5
and so on.
In A1 I have the following text: Distance: 457 meters
I want a formula N1 that isolates the numbers so it shows 457 only
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
View 7 Replies View Related I have the following SUMPROCUCT in place and it works fine for current purpose:
=SUMPRODUCT(VLOOKUP($E$9,Rota!$C$4:$FU$369,{5,30,53,78,103,137},FALSE))
columns 5, 30, 53, 78, 103, 137 all contain text. However I need to amend some of these to text but I still need the SUMPRODUCT to add up.
Is there a way to amend the above formula so its counts both text and numbers in 5,30,53,78,103,137 ?
I have the below list of numbers that I need a formula to convert to text and add zeros in the front of the numbers where there are not 6 digits.
207253
197706
8465
9085
102169
19667
183799
183297
Desired Results in Text Format:
207253
197706
008465
009085
102169
019667
183799
183297
Can numbers and text be included in the same cell and still have the number be included in the total in a formula in another cell? Or must a cell only have numeric values for it to be seen/included in a formula's total value.
I'm trying to create a database that totals materials for a construction project. I want to display the number of doors for a house in a row of cells and have the all the doors totaled in the last cell. This I have no trouble doing.
The problem arises when I want to add some text information about the style of each door in the same cell that the number of doors is shown. As soon as text information is added to a cell that has numeric information, that cells numeric information is not included in the final total in the last cell in the row.
I resorted to using comments instead, but, when the are made visible on the spreadsheet, they don't seem to lock to a relative position regarding the cell they're attached to. For instance, if I widen columns or make any significant spatial changes to the spreadsheet, the comments don't move with the changes.
There may be a way to lock comments to stay in a relative position regarding the cell they're attached to. And if that's the only way to make comments for the items in each cell stay with the cell, then I'll have to use that method. But I'd rather not have to use the comments function at all.
I'd much rather be able to have numbers and text be in the same cell, and still have the number value of that cell be included in a formula total at the end of a row of numeric information.
Example: (In this example separate cells that include both numeric values and text are indicated by parenthesis. The final cell that has the formula that totals the numeric information in the separate cells is indicated by brackets)
(30, raised panel doors, unpainted) (10, raised panel doors, white)
I have attached a file which shows some cells which start with "p" and then a number and some have the same but with the word " total" in them.
I would like to run a formula in the column next to it which will highlight which cells have that word in order that I can data sort a large file and delete the totals.
I think it will be an IF formula on cells that contain criteria.
Would anyone have a script for something like this?
I'm looking at range c5:c1200
Starting at C5
If the first 2 digits in that number start with 01 then I want the formula to be in G5 = F5*12
if the first 2 digits are 03 then I want G5 = F5*24
I have several of these if statement to put in However I need it to check the entire range as well because the formula may be different in each cell (G)?
I have to fill out forms for work and some of the info is the same and isd put on seperate sheets. On sheet one I have cells with the information that is the same on the different forms on shhets 2 and 3 i have the forms.
here is what i have having problems doing.
12-47136569
13x129077
How can i do a formula that copies the above values exactually to a cell ona another sheet
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.
This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. here is my formula
=SUM(IF((ProjectInfo!$AE$2:$AE$307 =$B59)*(ProjectInfo!$AD$2:$AD$307= AA$10),ProjectInfo!$AF$2:$AF$307,""))
projectInfo is the sheet where the list is. Column AE is the Item column on the projectinfo sheet column B on the summary sheet would be the item that i want to sum the quantities for Column AD is the section location column on the projectinfo sheet the AA$10 is the row and column of the section location i want to limit the sum of the quantities to the formula is in cell AA59 So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.
getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.
EDIT: Column AF on the ProjectInfo Sheet is the quantity column
I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))
How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).
I have data in the (various) formats as follows:
1) Text value here
1. Text value here
1.Text value here
1 Text value here
1)Text value here
Is there a way to take the cell contents from the first Alpha character? (i.e. no punctuation, no numbers). There are other numeric characters in the text values that I would like to keep.
I am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.
(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').
The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")
But it tells me 'numbers only'.
Basically...here's what I'm trying to do...
I have 7 numbers. The numbers are going to be grouped in threes.
(Ex. 123,124,125,126,127,134,....)
I want to assign column/range for each number...
1=A2:A100, 2=B2:B100, 3=C2:C100...
So for 123 the formula would be...
sumproduct(--(A2:A100>criteria),--(B2:B100>criteria),--(C2:C100>criteria))
It would be awesome to have the formula automatically calculated based on the numbers I choose.
I would like a formula to add up values in Col E based on a range of account numbers in Col C
For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.
Actual
Result
london#123;new york#34;
london;
new york;
delhi#145;chennai#54;
delhi;
chennai;
hyderabad#748;
hyderabad;
[code]....
removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.
