I have three columns. Lets assume i have One header row and 3 rows of actual data and the 4th row is for totaling column B values.
Column A is a col of "Ingredients", column B is the amount of the ingredient (from col A) used. The value is either in Oz or Lbs. Can be either. Column C is a yes or no column. If the value is Yes then i want to have the value in col-B included in my overall sum which is in the 4th row. If the value is No then i do not want the value in col-b (in that row) included in the overall sum.
I have a column named length and values are in different units ie few values in mm, few in cm, few in m. i have to filter out columns having range from 9mm to 6cm. How to do this task.(10mm = 1cm and 100cm = 1m)
Consider sample data as: 4 m 8 cm 9 m 3 mm 9 mm 6 m 6 cm 3 cm 2 m
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
I have a set of values. #N/A413636363632. The #n/a is there on purpose for charting reasons. How do I calculate the stdev of that range? i.e. ignoring the error?
I want to be able to look up and match several values in my worksheet to confirm, that an entry is valid. I would like the test to return an OK / NOT_OK value at the end of each entry. In the attached file you will find four different variables. In order to include an entry, it must be matched to find related entries, before it can be included.
For an entry to be OK, it must contain a company that has been listed in the same industry, in four different quarters (not necessarily in the same year). A company can be listed in several industries, but as long as there are not four entries in the same industry in four different quarters, the entries are not yet OK (hence NOT_OK). Therefore the only entries that are valid in the attached file are:
Company A Q1 2009 Administration OK Company A Q2 2009 Administration OK Company A Q3 2009 Administration OK Company A Q4 2009 Administration OK
I work in XL 2007, but have converted the attachment to 2003 for maximum compatibility.
I'm calculating a variance for a set of actual numbers vs. variance numbers. When there are no actuals, I don't want the formula to calculate a variance. How can I do this?
I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...
That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.
I am looking to build a VBA to do the following action:
Look up an ID number from column F (ID numbers are separated by an empty row at current), then check whether there is a corresponding value in columns N to P,
if there are values, copy them onto a different sheet into three different columns
if there values below this particular row, i.e. if the rows below say row 9 (where we looked up the ID number) in columns N - P are not empty, also copy all these values and add additional rows as needed on the sheet this is copied to
then look up the next ID in column F and do the same until there are no more IDs
I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.
I found the following formula which, when used as a named range, automatically extends a list for data validation:
[Code] ....
I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?
In a column I have data like 2W, 2BM, 4W, 6BM, 10W and 15BM. Question is how can I total all W and all BM. Total for W = 2W + 4W+10W = 16W and total for BM = 2BM +6BM +15BM = 23BM.
When using the replace rule in Excel, you can use "?" to represent a standard variable, e.g. if you had the text "Bottle 100g", and you set the replace rule as "?g", it would delete the "g" and the four characters before it, leaving you with "Bottle".
However, if I have lots of replace rules to make in a file, and I only want to eliminate the numerical values before a letter, how do I go about doing this?
For example, if I had the text "Dog food 10g", and I made a replace rule "??g", it would leave me with " food ". What I would really need, is a way of taking away the numeric values before the "g", which would give me "Dog food".
Therefore, is there a unique identifier for numeric and/or text values?
I need a left side padding for numeric values to keep them in 4 digits ie 1 becomes 0001, 25 becomes 0025 and 345 becomes 0345. Following is the custom number format I tried and it fails.
I have the following code and I am really confused to, why its not reading numeric values from the code below:
Code: Sub MG08Aug32() Dim Rng As Range Dim Dn As Range Dim n As Integer Dim Num As String Set Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
The above formula does not return any value and it simply appears as it is keyed. Cell AR85 has 5. I know it is something to with this cell, but I cannot fix it. In fact the whole Column AR has these values downloaded. I tried "text to columns" but it still doesn't work.
The Macro below will not combine numeric values Just Text. I get a Type Mismatch Error When I open It. I need for this macro to run with Numeric Values 1,2,3,etc.
It works if alphabetic and numbers are clustered together such as scs987 It works if alphabetic and numbers are not clustered together such as scs987dtg
Problem: It does not work when numbers are not clustered together such as scs987dtg1234
Nine Hundred Sixty Three Thousand Seven Hundred Eighty One Eight Hundred Seventy Eight Thousand Eight Hundred Seventy Eight Eight Hundred Twenty Two Thousand Seven Hundred Eighty Four Eight Hundred Twenty Six Thousand One Hundred Eighty Nine Nine Hundred Three Thousand Nine Hundred Six
formula to add letters but with a numeric value. this is for a schdule sheet. where w would equal 7.5 and x would be 0.
i am using this =SUMPRODUCT(--(ISTEXT(B3:H3)))*7.5 reads the w and adds up ok but need to be able to put w for work and x for off days and still add the total hours
I'm currently trying to create a rota timesheet which automatically takes shift patterns and deducts for unpaid breaks.
Basically if a shift is less than 4.5 hours long then no break is deducted If the shift length is 4.5 - 6.5 hours then 15 mins are deducted If the shift is longer than 6.5 hours then 30 mins are deducted.
The problem I currently have is all numeric values only are not being recognized on my password column. I have tried changing the cell formats but still problem still there. The program work if an alphanumeric value is declared as password. However if you simply indicate plain numeric values only as password, it returns incorrect values, it like it doesn;t recognize all numeric values. Also how can I make 0000 as a declared password? when I place 0000 on my password column, only one zero value is being displayed instead of four another thing if I happen to place 0123 only 123 is being displayed.
who can restrict the input values to numeric values only? I have 2 textboxes where the user enters employee id and numeric choice value of 1,2 and 3...I need help in making the textboxes restricted to numeric values input only. How Preventing the users from entering alpha and symbols values.
I have created a calendar in Exel for individuals to put in their time at the bottom for everyday I have put in a total. Is there a formula that I can sum the dates, pick a certain time from those date and get my total hours for that particular week?
I have the following issue. I have a file, which contains about 7 000 columns, each with 8000 rows, mostly filled with numbers (with decimals). But sometimes, numbers will be replaced by "N/A" where a value was not available. What I want to do, is delete columns, where all the rows are just "N/A" to reduce the size my file.
I have cell E15 in Sheet1 which will take an 8 digit Personnel Number, which can have leading 0's. I want to prevent a user from entering any characters other than 0123456789. I have worked out that I need to use the Worksheet_Change event and I need to use the function IsNumeric.
No matter what I do I can't trigger the Worksheet_Change event!
I have cells with alphanumeric values. I need to extract the numeric values based on certain criteria and put these values in adjescent rows.
The sheet I am using has 3 rows . Row B contains data. Row C is Amount, Row D is Month, Row E is Action
To put values in Rows C, D and E , I need the data from row B in following criteria.
data in B row is as follows :-
RS 555/STMT DT 170807/BP12/TO PAY MEMBERSHIP FEE RS.555/STMT 020907/BP12/ OFFER ANNFEE RS 555/- / STMT 020907 / BP 12 / FIRST TIME RS595/STMT 0807/BP12/ first time MEM FEES RS 525 /STMT 120807 / BP12/1ST TIM
criteria for row C if the cell contains "AMT" or "RS" or "INR" or "AMNT" or "RS."then extract the value till first "/" or "" and put it under Amount row. ( E.g: "AMT 595 /" or "Inr 595/" or " Rs. 595")
criteria for row D if the cell contains "stmt" or begins with "0" or endswith "07" then extract the value till first "/" or "" and put it under Month row.
criteria for row E
if the cell contains "BP" or "OP" then extarct the value till first "/" or "" and put it under department row. ( e.g " BP01 /" or "OP 02 ")
Are test have some text values RA = 0 and A= 0.5 as well as numeric values. I am currently using the formula =IF(COUNTIF(A1:A13,"A"),(COUNTIF(A1:A13,"A")*0.5)+SUM(A1:A13))/COUNTA(A1:A13) which is not very good at all, but it works for this instance. I also use the formula in B2 =IF(A1="RA",0,IF(A1="A",0.5,A1)) as a helper column but I need a stabile formula with out a helper column that will allow me to average text and numeric values. In the future, they are going to expand the text values part i.e. B = 0.3, BD = 0.2 etc....
I am trying to create a macro that determines if a range I am selecting has any non-numeric fields. If it finds say an cell beginning with a letter a message box appears letting the user know and possibly give the cell and value it found.
