I am trying to add a formula to a cell but I get this error, #NAME?
Here is the formula: =((pi * (5^2)) / 2) - ((5^2) * ASIN(1 - (A73 / 5))) - ((5 -A73) * SQRT(A73 * ((2 * 5) - A73)))
I'm trying to add it to cell B73,
how to change a proportion of text in a resulting concatenation
formula.
cell A1 contains the text in bold "ABCDEF", cell A2 containes "GHIJKLM" cell
A3 contains =A1 & " " & A2 but I want the cell A1 part to remain in bold. Is
there a way of using the TEXT function e.g. =TEXT(A1,BOLD) & " " & A2?
I have column A and column B:
I will be inputting data into column B.
I need excel to check to see if the data I input into column B is an exact match to the data in column A.
If it is an exact match, then column B will remain blank.
If the data in column B is different, I need column B to show the following:
No match: <data>
Example I input in column B the following:
Column A Column B
1. Car Car
2. 4357 9999
3. fsd34d 4erd
4. 98dkf 98dkf
Spreadsheet should show:
Column A Column B
1. Car
2. 4357 No match: 9999
3. fsd34d No match: 4erd
4. 98dkf
(Cell 1 and 4 in column B are empty because they are exact matches to Column A cell 1 and 4)
My questions:
1) How does the excel formula need to be written for this to work?
2) Is there a way to set it so that when I do a mass copy to data into column B that the formula will not be overwritten and it will still check to see if the data I copy and pasted into that column matches the data next to it in column A?
I have a spreadsheet that has been given to me by someone else, and unfortunately it has merged cells in it that are causing some trouble. I need to have a way to unmerge the merged cells, but for the data in the merged cell to then be copied into each cell that it splits into.
I've attached two spreadsheets - unfortunately I can't really attach the real one since it has research data in it, so I've reduced the size of the file and replaced some of the text. The first file shows how the file starts off, and the file named "result" shows how I would like it to be.
My real file contains some 10,000+ lines of data, and the merged cells are of varying sizes, with no consistency to the size, so I'd like a way to automate this instead of copy and pasting into every empty cell after I unmerge them.
I have a simple spreadsheet of an inventory. Each row on this spreadsheet represents the data related to an item. I would like for a user to be able to INPUT a serial number via a userform. Then, I would like for the program to OUTPUT some information about that particular item (a few cells that should be on the same row as the serial number).
I attached an image diagram that may better represent what I am trying to do.
Having some problems with a simple vlookup formula.
Never experiened this before.
I keep getting a #ref
Here is the forumla I am using
=VLOOKUP("JK09",Feb!A2:A9,13)
What I am expecting is for excel to look up the value of "JK09" within the range of A2:a9 within the worksheet Feb, and when it finds it it will give me the value in coloum 13 of the macthing row.
I have quarters of the year. I'm wanting to find a function wherein when I punch in for example year 2012, the cell would give me the sum of the 4Qs. If if change it to 2013, then it would give me the sum of 4Qs for 2013.
See attached sheet : sum from blocks of cells.xlsx
I am trying to combine text from multiple cells into one cell. I am referencing cells that reference cells on another tab. I have tried several formulas (see attached) but I have not been able to get rid of the zeros in the resulting string. I know I could reference the original cells, but I would like to find a solution to this without using VBA.
View 3 Replies View RelatedI have the attached table. I need to format with conditional formatting with the following criteria:
1) If >= 80% , then flag as green
2) If >=1% but <80%, then flag as yellow
3) If 0%, then flag as red.
I did the formatting in conditional formatting but the it did not results in the desired solution.
I am importing a .txt file into Excel that has two columns of dollars and cents. When I do my subtotals, all subtotals should be zero. Some of the totals show as 0.00, some as (0.00) and some as a dash which is what I would like for all of them. I have tried: ...
View 9 Replies View RelatedI am working on project of doing Turn around time Analysis of the volumes of work recieved before 12:30PM and after over a weeks time. My data consist of Work ref, Datein, Dateout, Timein, Timeout. To carry this analysis I have to Mannually filer the data as per Datein / Timein and Dateout and then count the no of work ref completed within Same day or one day or more than one day for each date of a given week. I recorded a Macro which gives count of no of work. for one particular date of week and repeated the same for each 5 working dates of given week. this Macro has gone to big and I want to use some looping technique which would change my filtering criteria. I tried doing this by assining a variable to Criteria but Macro gives no values.
Range("A1").Select
Selection.AutoFilter
Range("B1").Select
Selection.AutoFilter Field:=2, Criteria1:="=04/02/08", Operator:=xlAnd
Range("E1").Select
Selection.AutoFilter Field:=5, Criteria1:="<=12:30", Operator:=xlAnd
Range("IV1").Value = "=SUBTOTAL(9,H2:H272)"
Selection.AutoFilter Field:=9, Criteria1:="=L*", Operator:=xlAnd
ActiveSheet.Calculate
Range("sheet1!B3").Value = Range("IV1").Value
Selection.AutoFilter Field:=9, Criteria1:="=S*", Operator:=xlAnd
ActiveSheet.Calculate
Range("sheet1!B4").Value = Range("IV1").Value
I want to repeate the above code for different dates of week 04/02/2008 to 08/02/2008. How to change Criteria using Loopin technique.
I'm trying to figure out a function that allows to me to determine the exact month when I will breakeven. For example, my total net income for the 2 quarters of the year is 1050 and my investment is 500. If I have an income in Jan of 300, 300 in Feb, and 200 in March, then the function will spit out Feb as the breakeven month.
Please see attachment. Exact year to see when I breakeven.xlsx
I have a spreadsheet with a column that containts UNSPSC codes that can be in any of the four following formats:-
10
77/11
78/10/22
44/12/19/04
I basically want ta formula that will find and remove any / leaving me with
7711
781222
44121904
I have a work sheet which includes a column of numbers representing certain daily events. I am building a user defined function to analyse the trend in the numbers by assisigning values from -2, -1, 0, 1, 2 based on comparison of two days.
Below is the function I built but it is not working, it is resulting in zero values in most conditions. I have attached the sheet which includes the numbers and the function.
I am having an issue with my macro code. Basically I am trying to filter a pivot table by selected criteria. The code seems to be correctly filtering the pivot table but the table appears with no data in it unless I manually refresh the table. This defeats the purpose of automating with a macro. I have attempted a "work around" of just refreshing the pivot table after the filtering is complete but that was unsuccessful. Here is the macro code:
Sub BigReportMacro()
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField
Dim E
Sheets("Pivots").Visible = True
Sheets("Pivots").Select
'Refreshing all the Pivot Tables
[Code] .....
This workbook is designed to prompt the user with inputboxes allowing them to enter a name, hyperlink a file to that name, and then a date. Next all the drawing names are read into an array and stored. Along with some minor sheet formatting, this is what happens when the button "Add Drawing" is clicked.
Next, when the button "Revise Drawing" is clicked, the user will be prompted with an inputbox for a drawing name. This name will be compared to the names in the array. If the name is found, the row containing that drawing will selected and the user will be prompted to update the hyperlink, then the "drawing date" will be moved to the right by one column, then the user will be prompted for another date replacing the date that was moved. The first sequence of commands or button works fine. The button "Revise Drawing" prompts the user with the inputbox requesting the revised drawing name, then develops the runtime error 1004.
Using Excel 2010...
I have a list of names (text string) in a column (A) that have associated whole numbers in another column (B). What I would like to do is create a formula that multiplies the text string in cell A2 (for ex.) by the whole number in B2 and return a "list" of the text string in A2 equal to the whole number by which it is multiplied.
Here's a visual example of the results I want to achieve:
Using the above example, I would like to end up with a list in column D with 159 total rows with the text contents corresponding to the whole number in column B.
I have created a monster Excel file that contains loads of vlookups and is 95MB and extremely slow.
I guess that vlookup creates tables in the background, hence the file size. Does anyone have any ideas on how to decrease the file size while keep the vlookup functionality?
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
Note: I know the syntax below is not how you would enter forumlas into Excel but I am using it to quickly illustrate what I'm trying to do and need help with. A cell not in column D, E, or F contains a formula of the form: F=IF(D>0, D/E, "blank") .
Note: If a cell in column D>0 (eg. D5>0) then the cell to the right of the selected D cell (eg. E5) will also be >0 ; otherwise, both cells will be empty.
This is what I want to do: If cell D?>0 , then F?=D?/E? ; otherwise, F?=empty .
Example 1: If D5>0, then F5=D5/E5 ; otherwise F5=empty .
Example 2: If D7>0, then F7=D7/E7 ; otherwise F7=empty .
Etc. I want this to apply to all rows.
I cannot enter the formula directly into the F? cell because sometimes I will need to enter data into cell F? manually. When I need to enter data manually into F? this results in the formula being overwritten by the new data and this means that if I ever needed to have data calulated based on the formula F=IF(D>0, D/E, "blank") I would have to re-enter the formula from scratch over again; obviously this is a nusance.
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.
I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.
When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.
i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6
this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads
I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
I'm wondering if it's possible to write a formala that if a certain condition is met, performs a formula referenced in another cell. For example: Column A contains a list of formulas. In column B is the condition, let's use Work Type as an example. The result (or the formula I need) would be in column C. So if the work type is "Type1", then the formula in the corresponding column C cell would perform the formula in cell A5. If the work type is "Type8", then the formula in the corresponding column C cell would perform the formula in cell A2.
Is this possible to do without VBA?
i need a formula for which calculate all cell (calculating years months and days formula). FIND ATTACHED .xls file i need a answer in cell highlited in yellow color.
View 3 Replies View RelatedI have attached a sample of the spreadsheet. The cell/cells in question are f32:f36 on the first tab.
Question #1 how this formula works or what it does.
Question #2 Is when I enter the required information in cells a32:e32 it enters an N/A in cell f32. When I than click on cell F32 and than click on the formula bar and hit enter the information populates.
I have two questions:
1. Is there a way cells with formulas cannot be tampered even if they are not locked? So when I enter a data on a cell with formula, the formula won't be erased? The sample below shows that C1 and C2 has formulas but let's say I enter a value on C1, the formula should still stay? Is this possible?
A B C
1 5 10 =SUM(A1:B10)
2 2 4 =SUM(A2:B2)
2. Let's say I have a total of 125 (A1) and I need to get the answer (B1) where it has 5 with No. 3 on top (subscript) of 5? How to create a formula for this (I hope I make sense)?
In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.
Examples:
=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "constant".