cell AT7 is a formula which brings back a numerical value i.e. 15.
in cell AS7 i need a formula that displays the value of cell AT7, as long as that value is less than 15, if the value is 15 or higher then 0 should be displayed.
I dont know why i cant get this, seems like a simple IF statement to me, but who knows.
Once this formula is done i need to drag it down about 2500 cells and autosum to get a column total...
i used filters to order addresses, however today i filtered my address column and all the addresses starting with 1 lined up before those starting with two, instead of in numerical order, is there a way to fix my sorting so it goes back to numerical instead of alphabetical?
View 4 Replies View RelatedI'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies View RelatedI want to set up an if statement that does the following, where 'a' and 'b' are numerical values.
If 'a' is not a multiple of 'b' Then
...
Else
...
End If
How can I write this so VBA can understand it?
I'm looking to use an if/and statement to give back a true of false but it doesnt seem to be working, I'll give an example, I want excel to search a set of cells to see if its blank and another cell to see whats written there(from a pull down menu) and if both of them are true I conditionally format to go green, otherwise stay white. It works for one cell, condition below:
=AND(Anoop!B3="IIC",NOT(ISBLANK(Anoop!C3)))
but I cant get it to do this
=AND(Anoop!B3:13="IIC",NOT(ISBLANK(Anoop!C3)))
i.e search the entire column
I was also wondering if it was possible if it found an "IIC" in B8 how I could get it to check C8, D8 etc..
when i position the cursor over a cell, i get muliple cells highlighted. i enter data and it is entered in the cell i want but the others being highlighted is confusing. i checked and i am not in extended mode.
View 9 Replies View RelatedIn sheet1 there are three columns
A1 : PO Number (for example 111001)
B1 : PO Line (for example line # 91)
C1: Amount ( USD 50.00 )
In Sheet2 there are three columns also
A1 : PO Number (for example 111001)
B1 : PO Line (for example line # 91)
C1 : Account Code ( For example AA.00.000,725001)
Now, I would like to use a function that brings the value of C1 in sheet 2 ( The Account Code) to d1 in sheet1 taking into consideration that the PO number may repeat but the line numbers is not repeating for the same PO. Attached is a sample for better illustration
I have several thousands lines of data....much of the data is the same, 2-5 rows per person, but at the last two columns is different numbers/totals....I'd like to get those all into their own column so that each person has one row with all the data...I USUALLY would sit and sort by each total, shift them over to the right into their own columns, then sit and shift them all up to one line...but thats a carpal tunnel project and i know there must be an easier way....subtotalling brings the numbers down to one line once I have shifted each one over, but not the rest of the data....
Pic Attached: Excel problem pic.JPG
I have a column of dates (2007, 2006, 2005 etc...) and a column of data ranging from "unsatisfied" to "very satisfied". I want a formula to provide, for example, the total number of occurences of "very satisfied" in "2006".
View 9 Replies View RelatedIt's a spreadsheet of about 300 rows, each row corresponding to a certain department. each row is filled with various data, both text and numerical, including various different scores and averages. the columns include daily scores and weekly averages. I want to be able to create a new column that will calculate the time (in days or weeks) since the last score/average was entered.
1. a formula that will return the address of the last numerical entry in a given row. (I've got a MATCH formula that will do this, although it doesn't let me specify different cells or ranges, only the entire row)
2. whichever cell is returned will give the date instead of the value. (for example, if the last value entered is in $F$63, and Column F is headed 23/11/09, then the formula will give the date)
3. then finally, calculate the time between that date and the present time
I've tried various different things, both in normal Excel and the VBA editor, and I'm beginning to think maybe this isn't even possible.
I'm putting together an Environment Threat Assessment for work and want to automate as much of it as I can to alleviate the amount of time ppl have to spend putting values in. My main problem is I have three very important columns: Likelihood, Success Rating and Risk Rating. Likelihood and Success Rating values will be selected from the following:
VL - Very Low
L - Low
M - Medium
H - High
VH - Very High
Each of these is given a value: VL = 1, L = 2, M = 3, H = 4, VH = 5.
The Risk Rating is the result of multiplying the Likelihood and Success Rating. So a Likelihood of Medium and a Success Rating of Low will give a Risk Rating of 6. Therefore, the cells on my spreadsheet would look like this:
Likelihood Success Rating Risk Rating
M L 6
The issue is I want a couple of things to occur. Firstly I want keep the values in my Likelihood and Success Rating cells as letters (ie. VL, H, VH etc) rather than numbers, while still generating a number result in the Risk Rating cells. Secondly I'd like to be able to change the colour of the Risk Rating cell to reflect the number it gets assigned. For example Green for < 8, Amber for 9 – 14 and Red for 15 – 25. I've been trying to do this with array formulas and am basically going around in circles with no success.
I have a sheet with thousands of rows of ID's which are made up of a series of letters, numbers, underscores and or spaces. Within the sheet there are numerous duplicates so I need to able extract the numerical element, which is the key data to be able to identify the duplicates.
The numbers I need to extract are mainly 6 and 7 digits long however don't always sit in the same place within the ID. I don't need the 3 digit number sequences, just the 6 & 7 number sequences
e.g
AB_123_456789_Jones
CD_131_7654321_Smith
AB_123_Jones_456789
I've found a formula that strips out all the numbers, however it also included the 3 digit numbers which I don't want. I've also used Text To Columns to break up the ID across a number of columns however that leaves me with 2 and sometimes 3 columns where a 6 digit ID appears
Is there a simple way to extract the number if it's a certain length? i.e. 6 or 7 digits long
I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:
2008 2009
$23.95 $24.15
Using excel 2007
Is it possible to make a back-reference in a cell formula similar to regular expressions?
Example:
=IF(I15=FALSE, [H17 * INDEX(Percentages,I17+1)], IF(A17="", $1, 0))
...where [ ] indicates the actual reference and $1 indicates the reference number
I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").
=IF(AND(DJ10="HH"),COUNTIF(DF10:DJ10,"HH"),"-")
I use excel 2002.
I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
I have a layout something like the following:
A1
A2
A3
[Code]....
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
A1
A2
A3
B1
[Code].....
Here is my formula:
=IFERROR(INDEX(Stored!$D$22:$AN$795, MATCH(D11,Stored!$E$22:$E$795,0), MATCH(E10,Stored!$C$22:$AN$22,0)),0)
For some reason it keeps pulling back the value that is one cell to the right, so one column off.
What could it be.
In this example it should pull back N363 and it is pulling back O363.
know if there is a formula for counting back a year from a date?
Basically what I have is we operate a rolling absence/ sick year and so what happens is on a staff members return from absence/ sick we look back exactly one calendar year from the last day of absence and count the number of days off absent in that period for comparison against their paid allowance.
I have posted a mock up sheet and what I am looking for is a formula for column C. The highlighted cells are the period it would be if today was the last day of absence.
Sheet1 Â ABC1Days AbsentDateRolling2201/01/2010Â 3302/02/2010Â 4403/03/2010Â 5207/06/2010Â
6310/11/2010Â 7431/12/2010Â 8601/01/2011249202/02/2011
2410103/03/20112211307/06/20112112510/11/20112413201/01/20121914317/01/201216
Name Qtr Year
Org 112007
Org 222006
Org 332006
Org 442006
Org 542007
Org 612006
I want the code to say, If Qtr = 1 And Year = 2007, then bring back Org Name, If Qtr = 2 And Year = 2006, then bring back Org Name and so forth.
The code should loop through a range of constant data (Region Name), until the Region Name is blank.
Dim OrgCell As Range
Dim OrgTargetCell As Range
Dim RegionCell As Range
Set OrgCell = Sheets("DATA Removed").Range("A2")
Set OrgTargetCell = Sheets("Overdue").Range("A3")
Set RegionCell = Sheets("DATA Removed").Range("C2")
i = 0
OrgCell.FormulaR1C1 = _
"=IF(AND('DATA Removed'!R[-1]C[11]=1,'DATA Removed'!R[-1]C[12]=2007),'DATA Removed'!R[-1]C,0)"
Do
OrgTargetCell.Offset(i, 0).Value = OrgCell.Offset(i, 0).Value
i = i + 1
Loop Until RegionCell.Offset(i, 0).Value = ""
End Sub
I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell.
I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.
I was wondering whether there is an easy way to change a formula containing a named range back into it's original cell reference in VB. For example I have a cell saying =cogs2008/revenue2008 and I want to change it back into =A2/A3. Any ideas?
I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row.
We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months.
For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets.
In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month.
Here is the formula:
=(FVSCHEDULE(1,M79:M90/100)-1)*100
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
my output needs to be partno, date due, quantity.
=MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx
Following on from a previous post need to add to my (forums) array.
I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
View 2 Replies View Relatedif-and-or statement if possible. The formula would go in cell A10.
If D5="x" and D4=7, answer would be "H8", if D4 is blank, answer would be "x".
If D5=2 or 3, and D4=7, answer would be "H8", if D4 is blank, answer would be "v8".
If D5=1 or blank and D4=7 or blank, answer would be " ".
I am a sales rep and am designing a spread sheet to help me track my commissions, on occasion I need to split the commission with another rep I have the spreadsheet set up as follows:
J1 Dollar amount - the total amount of the sale. K1 fee% - my fee is negotiable so this number will vary by client this field is formatted as a precentage. L1 total commission - takes J1*K1 to give me a $ amount M1 split y/n - here is my question, i would like a formula that will divide L1 in half if I type a Y or Yes in M1 I'm sure this can be done but I dont know how, also would the formula go in L1 or M1 if I want the results to be displayed in L1?
I'm entering a simple IF statement in excel that asks if one cell is bigger then another. However one of the cells I'm referencing is also a formula, so excel is not reading the value in that cell. How can I get around this problem? Is there anything that will enable me to compare just the value of that cell? I have attatched the file and the cell in question is F21.
View 5 Replies View RelatedHow to increment a formula with an If statement that transforms 2 values in time format and then as long as these two values keep beeing added in the cells the formula keeps active in the column.
I tried to apply the code for all the but this makes my file huge like 20MB!
You can check this on the file, go the "records" sheet, add the time in the "shift start" and "shift end" fields, then the difference value goes to "hours" in the sheet.
[URL] .....