Put Together An IF Statement Which Will Give Me Certain Output Depending On The Particular Value Within ONE Cell
Mar 5, 2009
I am trying to put together an IF statement within excel which will give me certain output depending on the particular value within ONE cell.
For example:
If cell L5 has a value between 0 and 1.00 output value 0
If cell L5 has a value between 1.01 and 2.00 output value 20
If cell L5 has a value between 2.01 and 3.00 output value 40
If cell L5 has a value between 3.01 and 4.00 output value 60
If cell L5 has a value between 4.01 and 5.00 output value 80
If cell L5 has a value between 5.01 and 6.00 output value 100
View 4 Replies
ADVERTISEMENT
Aug 29, 2012
I have a nested if statement that doesn't give a blank cell when it is evaluated. The cell is general formatted but results in a '0' in the cell instead of a completely blank cell.
If statement is:
=IF('Sales info'!B16="",'Sales info'!B6,IF('Sales info'!B6="","",'Sales info'!B16))
Why is this giving me a 0?
View 9 Replies
View Related
Jul 10, 2009
What I would like to do is output the first 4 characters of a cell into another cell. eg Cell A1 has "1234 Sydney". If I just use the manual formula "=left(A1,4)" it would give me the correct output "1234" in say cell B2, but as a formula. As I need to automate this in a macro, how could you code this in VBA to output the String "1234" into cell B2(ie the actual output rather than a formula)?
View 2 Replies
View Related
Jan 21, 2010
I know that I an probably asking for too much but I must get the formula some way. OK, I need a formula for this:
lets say I have 3 columns (A,B,C (search criteria). In each Column there is a DIFFERENT number. There is 3 more columns (D,E,F (used to compare).Each column has 3 Different numbers. If 3 numbers match between the two sets of 3 numbers I want Column G to display "CCC". IF 2 numbers match I want G to display "CCH". If 1 number is a match I want G to display "HHC". If none of the numbers match I want G to display "HHH".
I can manually do these but it will take waaaay toooooooooo much time to do. If you can do this for me it would be a great help indeed because I'm trying to do other states. With this formula I can use Automate to do a large quantity of numbers.
View 14 Replies
View Related
Jun 7, 2009
I have two sub-system tabs (IAS & CCTV) and one calculations page (estimate). I need G3 (estimate) to give me the total price of hours sold on a project.
Because the systems hours can be marked up differently I wrote an average formula. But I need to add an IF statement saying if there is no hours in the sub system then ignore the hour price.
if this formula doesnt take this into account and I delete H3 (CCTV tab) then the overall price of hours sold in estimate will be wrong.
I have attached the sheet.
View 10 Replies
View Related
Jan 16, 2007
Is it possible to return a value, via an IF statement, depending on the font style of a cell?
What I want to do is this...Check to see if a the font in the cell has a strikethrough, if it does, return 0, else return the value in the cell.
View 9 Replies
View Related
Oct 10, 2008
i'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
=IF(L5>2,"YES")
View 3 Replies
View Related
Aug 3, 2012
I keep a formula to many arguments error.
=if(C2="","X","", ), IF(h2="","X","", )
What I am trying to say is if cell c2 is populated enter a "x" and if h2 is populated enter a "x".
View 4 Replies
View Related
Nov 26, 2006
How can I get this to read each, (if (and statement) and give the correct answer. And not give me the 1st underlined statement answer in cell D10. If that also equals true.
But if any other cells G11 thru G14 equals OUT. It still will give the 1st statement.
I need the answer to be the only one of them correctly that’s equal true.
I know that the 1st underlined statement = true. Because G15= OUT .....
View 9 Replies
View Related
Oct 2, 2007
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..
View 9 Replies
View Related
Nov 21, 2013
I have created the If statement below, in a nutshell its comparing current month performance to last month, stating if it has increased or decreased by last month and how much percentage by
example output would be 'This has decreased by x%"
my problem is when it states the performance has increased , the x% is coming as a negative number (-x%)
what can I do to change this to positive?
Below is the formula
=IF(INDEX('Red Activity & Performance'!$E$45:$J$45,MATCH(PERFORMANCE!$AK$31,'Red Activity & Performance'!$E$44:$J$44,0))
View 7 Replies
View Related
Apr 23, 2013
Excel 2010, I have the following list (showing part of it):
Name
T-Shirt Sizes
Nathan
S
[Code]....
The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.
Thus, I've been trying to use the IF statement with the following logic but to as no avail
IF Size in Column is XS
Add 1 to a particular Cell
IF Size in Colum is S
Add 1 to a particular Different Cell
and so on for the other sizes...kind of like a counter for a for-do-loop.
View 2 Replies
View Related
Feb 6, 2009
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
View 2 Replies
View Related
Jul 27, 2006
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
View 2 Replies
View Related
Jul 31, 2009
I am trying to write a formula using If statement to output the result "True".
I have 4 columns (P3, T3, AD3, AK3) that store a value of either "Yes" or "No", I want to check the column if they are equal to "Yes". I have written a formula but it only works for 1 column at the minute, i not sure how to add the other columns into the formula:
=IF(P3="Yes","True")
View 2 Replies
View Related
Oct 24, 2008
Excel 2003
Lets see if I can even explain what I want to do.
will have a date column
Will have a total calorie column (this will be a sum of several column containing individual foods)
On a separate worksheet (food sheet) will be a list of foods in one column and their calories (per serving) In the next column.
On the first sheet I want a drop down that lists all the foods on the "food sheet). selecting from this drop down will use a vlookup (or hlookup - depending) to arrive at a caloric value.
So here is (are) the problems. (and the solutions need to be NON-VBA)
1. How do I tie the drop down selection to a vlookup (macro maybe)
2. How do I use that one drop down to populate any cell on sheet one?
can this be done without coding?
This will be an exercise for an excel class to teach vlookups, linked sheets,
making a dropdown, using a macro, formulas, IF statements............
The class is probably not ready for VBA which is why this needs to be a non-coded solution.
I am open to other solutions (related topic) that can bring together all of the above learning concepts
View 14 Replies
View Related
Dec 27, 2007
I have a LOG sheet where I am getting all my data from,
and a TIME and DUTY sheet..
COL A Has Dates, a4 to a34, Limited to 31 lines for the dates
and in col F I have # values, (1.2-2.2 etc).
The formula I have in Col F is
=SUMIF(LOG!$A:$A,#REF!,LOG!F:F)
but when i get to the end of the month, say for example the last 2 lines of the time and duty sheet, when a32 is 29/ Feb, the next row is blank and same with next row. (leap year).
How do I get the formula above to ONLY calculate when the cell in COL a on the same row, when there is a DATE value in COL a?
View 9 Replies
View Related
Mar 8, 2012
I am currently using the .resize function to give a specific value to each cell within a range.
First of all, is it the most efficient way to do it?
If so, why am I getting error "Application-defined or object-defined error"
with this code :
Code:
ThisWorkbook.Sheets("Merge").Range(Cells(3, 2), Cells(4000, 2)).Resize.Value = "TEST"
View 2 Replies
View Related
Jul 27, 2007
Does anyone have any clue of a VBA function that gets a cell and Changes the type of it´s contense?
I have been using the Cstr function as in the next example but I dont get the result I want
hojaRES.Cells(i, 6) = CStr(hojaRES.Cells(i, 6))
If IsNumeric(hojaRES.Cells(i, 6)) Then
MsgBox "nothin changes :("
Exit For
End If
hojaRES.Cells(i, 6) = "0" & "34" & hojaRES.Cells(i, 6)
My problem is that i want to add 034 prefix to a telephone number, and as it treats the data as numeric; number 0 (before34) is deprecated
View 3 Replies
View Related
Jul 22, 2014
I am a teacher and I want a column to turn a particular colour if a pupil has met their target grade and another if they have not. Our grades work as letters A* being the best then descending as follows A, B, C, D, E, F, G, U.
I have a column with the pupil's target grade (one of the above letters) and another column with their current grade. How do I get excel to understand the ranking of the letters and format the current grade column according to whether they have met their target or not?
View 8 Replies
View Related
Dec 3, 2009
I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10
[A4]=B(row())*K(B1)
View 4 Replies
View Related
Oct 8, 2009
when a cell in column "K" is clicked, an input box gives the user the choice of entering a number between 1 and 12. That value then inserts row(s) based on input value ..
View 6 Replies
View Related
Aug 11, 2008
I am trying to give a cell a unique ID based on the contents of 2 other cells.
Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.
View 9 Replies
View Related
Oct 1, 2008
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies
View Related
Sep 30, 2011
Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:
Cell A1: 0.003529
Cell B1: 0.48
If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.
Why does using cell references in my formula give me the incorrect value?
View 2 Replies
View Related
Sep 10, 2013
How do I paste a text paragraph into Excel and give each individual word into its own cell?
View 6 Replies
View Related
Feb 15, 2010
I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)
View 4 Replies
View Related
Jan 27, 2014
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
View 3 Replies
View Related
Mar 22, 2009
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
View 4 Replies
View Related
Sep 18, 2006
Im working a spredsheet for my work, it is a database of qualifications for certain employees. There are about a hundred different qualifications and about 20 different employees. And some of the qualifications overlap. I would like to input a certain date in one of the cells, and have it copy into some of the other cells where the quals overlap. But i would like to input that date into any of the cells that overlap, not just one.
View 12 Replies
View Related
