If Then Formula - If Value Is Higher Than Identified Cell Then Other Cells Populate?
Jan 6, 2014
I am trying to determine how to great a formula for cells E5, E6 of the attached (in pink) that says if the value of the cell above (E4) is equal to or Greater than the value of cell E10, the cell will perform the formula that I already have in E5, E6.
But what I'm trying to do is create a 5 year investment plan whereby the worksheet automatically inputs those values for me if the balance is above our minimum reserve. That way I can play with the default assumptions and see how it changes throughout the worksheet.
View 1 Replies
ADVERTISEMENT
Feb 12, 2013
I would like to compare two cells side by side and populate the higher of the two cells in one color and the lower in another
View 3 Replies
View Related
Jul 13, 2013
I want the cell A3 to be the higher value between the cells A1 and A2 ....
A1 A2 A3
20 30 30
View 1 Replies
View Related
Aug 24, 2009
I have a spreadsheet that I am using to track competitor retail pricing
The problem is that I have a list of values and I want to be able to show how many of these values are either higher, lower or the same to a specific cell value. However one complexity that I have is that some of the competitors do not have values for all records (i.e. some are blank) and I want to discount these from the counting.
View 12 Replies
View Related
Jun 21, 2007
In the sample worksheet, there are columns "Min", "Max" and "Average". The next 2 columns "Remark1" and "Remark2" I have formulas.
In column D "Remark1", I use this:
=IF(IF(C2="",0,C2)>B2,"Ave>Max",IF(IF(C2="",0,C2)<A2,"Ave<Min",""))
In column E "Remark2", I use this:
=IF(B2=0,IF(B2<A2,"Min>Max",""),IF(B2<A2,"Min>Max",""))
How can I include col E formula into col D formula, so that i do not need to type anything in col E and such that consolidated formula in Remark1 will populate value in Remark2? Will Offset formula do the justice or need a VB code to do the job,
View 5 Replies
View Related
Oct 10, 2009
Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?
View 14 Replies
View Related
Mar 31, 2014
I have a spreadsheet with data populated down column B. This size will vary from day to day so is there a macro I can run that will populate Column C with a formula based on their being data in the adjacent cell.
View 2 Replies
View Related
Aug 15, 2008
I have an index page with hundreds of worksheets (poor spreadsheet design i know) I am looking for a button to go next to the sheet name on the index sheet, read the cell in the button's macro code and when pushed go to that sheet's location. I can easily make a button to go to one sheet but with hundreds of sheets this becomes a monotonous task. Example
Index Sheet
Sheet 2 (Button) <-- Button reads the text, takes you to the "Sheet 2" location
Sheet 3 (Button)
Sheet 4 (Button
Sheet 5 (Button)
View 5 Replies
View Related
Jan 19, 2009
If a cell is not blank, then increase that amount by a percentage identified in another cell and display the value only without any formalas in a new cell. To this end I wrote this IF statement:
=IF(I2>0,CP9=(CA9*I2/100)+CA9,CP9="")
Observation: I see a FALSE in CS9 where this IF statement exists, however, CP9 does not display the required value.
View 8 Replies
View Related
May 1, 2012
I am looking for a formula that will show the higher value of two cells
i.e.
A1 = 10
A2 = 12
A3 = formula to say look at A1 and A2 and show the higher number
View 2 Replies
View Related
Oct 2, 2009
What formula can I use to accomplish the following, if it can be done.
If B9 is 100% all the rest of the % cell inputs would be 0%
If B9 is less 100% ,lets say 50% all the rest of the % cell inputs would be less than or equal to 50% until any sum of those cells equal to 100%
in which case if I enter 50% on B9 and 25% on D9 and 25% on F9 their total sum would be 100%.
If I try to enter a % on H9 it should not allow me to do it
because then the total sum on O9 would be more the the original amount on A9
View 6 Replies
View Related
Mar 30, 2014
I need a formula that can look at to cells, and if they are both the same pick cell A1 but if one of the cells has a higher amount than the other pick that one.
so the formula would do the following
look at the amount in A1 and B1 if A1 is higher than B1 input A1, if B1 is higher than A1 input B1 but if A1 = B1 then input A1,
View 3 Replies
View Related
May 29, 2013
I am trying to do the following.
Monday=0
Tuesday=5
Wednesday=10
Thursday=0
Friday=5
Saturday=0
Sunday=0
Averages
Monday=0
Tuesday=5
Wednesday=7.5
Thursday=0 (I have got a value return of 7.5)
Friday=5
Saturday=0 (I have got a value return of 5)
Sunday=0 (I have got a value return of 5)
I need to work out averages for cells higher than zeros, in other words, I need to ignore those.
Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.
I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").
I would also like to hide the annoying #DIV/0! error.
View 9 Replies
View Related
Jul 25, 2014
I have a column of values (E4:E87). I'm adding 1 value to this column manually each day...so tomorrow (July 24th) I will have E4:E88...and so forth. Each day is a new row.
In column Z (i.e. cell Z87) I have the formula =(SUM(O53:O87))/(SUM(E53:E87)). Z88 will have
=(SUM(somevalue:O88))/(SUM(somevalue:E88)). This gives me a resulting percentage for a rolling period of time.
I need an automated way to populate "O53" and "E53" ("somevalue") in the formula so that the SUM of the current row's cell (i.e. E88) through [whatever] is 50 or greater. (The sum of the values should be as small as possible, but at least 50, and should not include any older values than absolutely necessary for it to equal at least 50.)
An image of my spreadsheet can be viewed here: [URL]
View 3 Replies
View Related
Apr 8, 2014
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
View 3 Replies
View Related
Oct 12, 2009
I am trying to work out a formula. I know it's a simple one but i can not work it out. If i work 9.5 hours or more per day I get an extra $10.50 for food allowance.
For example (see attachment) IF E17 is equal to 9.5 or higher then cell E23 should automatically read $10.50
View 3 Replies
View Related
Jul 7, 2014
I am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.
For example, if worksheet 2 has the following:
Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2
and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.
View 5 Replies
View Related
Jan 24, 2014
I need to be able to populate a cell with text from 3 possible cells, two will have the text "none" in them, the other text cell is the one i need to populate in another cell.
View 3 Replies
View Related
Apr 6, 2013
I have a table in excel with some data to create labels, but these labels must be repeated according to the number of volumes. Example: I have a delivery for X and such delivery has 5 volumes, need to create 5 labels just changing the volume number: 1/5, 2/5, 3/5, 4/5 5/5.
I would enter a value in "D1" after running the code, in column "A" shall be filled as follows.
D1 = 7
A2 = 1/7
A2 = 2/7
A2 = 3/7
A2 = 4/7
A2 = 5/7
A2 = 6/7
A2 = 7/7
If "D1" equals 3 then
A2 = 1/3
A2 = 2/3
A2 = 3/3
View 4 Replies
View Related
Jul 9, 2012
So I have a table where column B contains a text entry, and column C returns a certain other text entry based on if certain keywords appear in the B entry. Say, if B8 contains "Fresh Apple Pie", column C8 could return "Tastes Delicious" by searching for "Apple" in B8. I've got it work just fine using a ridiculously long If(Isnumber(Search())) function, but the problem with this is that if I want to adjust the output, or add a new input rule I have to go into the formula and that just gets messy. Instead, I'd like to start an extra sheet, or a table to the side, where the user can write in column D what he's looking for, and then in E what the formula should return. Ie., if I wanted to change "Tastes Delicious" to "Getting Sick of It", I'd just have to change the E8 entry to that text, but leave D8 as "Apple". I've been playing with array formula's, but I can't get the formula to search past the first row of criteria.
Basically, I want something like this, where columns D and E are open ended for any user to add new search criteria or change the existing (for example, I could put "Gummy" in C4 and "Candy" in D4, and B3 would then return "Candy"):
Item
Output
Search For
Classify as
Candy Apple
Fruit
[Code] .....
I've been using this formula:
{=IF(ISNUMBER(SEARCH(C3:C4,B12)),D3:D4,"")}
(the cell references are different, but you can see the formula)
And it half works. If C3 is found in B12, it'll return D3, but if C3 isn't found I need it to look for C4, which it doesn't do. Naturally, the end formula will be a much larger range than two cells though.
View 6 Replies
View Related
Apr 9, 2012
see the example below...
ABCDE1blueblue2greengreen3redred4purplepurple
I basically want column E to check the cells from left to right and display the value that is in the first populated cell. For example, cell A2 is blank, therefore, it should display the value from B2. A3 & B3 are blank, so E3 should display the value from C3 and so on...
Is this an "IF" statement?
View 2 Replies
View Related
Jun 12, 2009
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
View 2 Replies
View Related
May 12, 2014
I've attached an example workbook.
Trip Plan example.xlsx
I'm trying to work out how to have cells R11 - Rxx generate the correct date, based on the rest of the calculations.
Please note that the attached is a crude cut down version of the larger spreadsheet, for the purpose of highlighting my problem.
SO, if E7 is changed, subsequently the values in K11:Rxx will be respectively updated.
D12 and G12 are entered manually. (although I'll probably make these drop downs or date choosers at some point...)
Depending on the value in E7 and time in D12, the required break period is listed in K11: Kxx.
The time that these periods of rest are required to occur before is then calculated and listed in O11:Oxx.
I need R11:Rxx to calculated the appropriate date from these calculations, relative to the date in G12.
For example, the rest required in K11, is required on the same day, as such it need to populate with the value in G12.
However the rest required in K13:Kxx, is required by a time (O13:Oxx) on the following day - G12+1
In some case, this time variance could run over 2 or more days.
I figure it's got something to do with the value in D12 also including the date of 0/1/1900.
I'm happy to add hidden cells for formulas/results to work around it, as the end product will print as an A4 document, where many cells will be locked.
Would prefer not go with any VBA unless it's my only option as I haven't done any VBA stuff as yet.
NB (the value in P5 is static only in this example)
View 11 Replies
View Related
Mar 25, 2014
I have contacts list that I made and want to take it to the next level, but don't know how. See attached.
The goal:
1. Highlight an entire row of someone's contact info (i.e. row 9)
2. Click the "ORDER FORM" text box to activate a macro that populates specific cells on the 'order form' sheet with the customer's contact information from the highlighted row. The information I want copied over is in red on the order form sheet. THAT'S IT!! (Oh, and 'paste value' the current date from H5 so that the date is static on the order sheet
If possible, I would like to have an error check in case no row is selected prior to clicking on the text box that pops up the note "Please highlight a contact row before clicking the 'Order Form' button."
View 5 Replies
View Related
Dec 2, 2009
How do I automatically insert rows given the identified quantity to insert and copy the contents of the previous item on the insert rows. Does that make any sense
a1 - abc
a2 - def
a3 - ghi
I need to insert 10rows below a1, and copy a1 across the inserted rows. Now I have a ton of this case currently do it manuaaly, it might take me a lifetime to complete.
View 5 Replies
View Related
May 7, 2007
I am looking for a way to ease a 24,000 line duplicate list into showing just the files from a specific folder/subfolder and its dupes. I have marked the files that reside in that folder by adding a column and entering a "1" in it. What I am looking for is a way to add that "1" to the others of that unique numerical ID duplicates, so I can sort and trim the list to just those files. End result is to show files in one folder and the locations of duplicates of those files elsewhere for eventual cleanup.
Very loosely, if C2=C1 and B1=1, then make B2=1 (and so on)
(A is blank and only there in case a formula needs input there)
A B C
110121
10121
118134
18134
18134
124232
24232
View 9 Replies
View Related
Sep 22, 2004
I am developing some code to extract selected worksheets to a new workbook, and provide the data in a "standalone" form where certain formulas have been converted to values etc. As part of this project, hidden columns are made visible, then data is pasted as values on a row by row basis, then the previously hidden columns are deleted. Here is the code that unhides the columns and identifies those columns as ones that it needs to delete later on:
'Expose hidden columns and keep a list
For i = 1 To 200
Range("a1").Offset(0, i - 1).Select
If ActiveCell.EntireColumn.Hidden = True Then
colnum = ActiveCell.Column
ActiveCell.EntireColumn.Hidden = False
If HideCOL <> "" Then
HideCOL = HideCOL & "," & colnum
Else
HideCOL = colnum
End If
End If
Next
The macro then goes about its business doing this and that, and at the end comes back to delete those offending columns whose numbers have been stored in the variable "HideCOL".
'Delete columns that were hidden
If HideCOL <> "" Then
Dim colArray As Variant .............
View 3 Replies
View Related
Nov 29, 2010
I'm creating a configuration tool for instrumentation that has several options. Many of these options are dependent on one another. To summarize, I need to:
*Conditionally lock cells (and populate with "N/A") OR allow selection from drop down list - the list exists, it's the locking and auto-population I'm struggling with
Example: If A1 = No, then B1 = "N/A" and is locked, else allow selection from drop down list in B1
View 3 Replies
View Related
Jun 5, 2013
I have a scheduling tool that I need to do the following with -
Allow user to select a range of cellsUser types a value (non formulaic just plain text)Value gets populated to all selected cells
The reason I'm not just using fill handles is because of the amount of formatting in the cells.
View 6 Replies
View Related
Sep 22, 2009
I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.
View 3 Replies
View Related
