Return 1,2 Or 3 Based On Result Of Formula
Mar 3, 2007Any other day but today (I've been starring at numbers too long) I could do this.
I need a simple formula that will return:
a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if
Any other day but today (I've been starring at numbers too long) I could do this.
I need a simple formula that will return:
a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
if Cell F42 contains 50%, then my fomula returns "wrong"
=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))
how do I get the formula calculate correctly?
I have been trying this for hours but to no avail.
I have a table with 4 columns headers
Name, Amount, Loc and Code
The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB
I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if
1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same
I have attached a sample to illustrates the result
I have four columns, A through D
Column C is returning a simple vlookup of A
I need Column D to return a value where C is TDMA return TDMA or when C is GSM lookup column B compare to tab2 (columns A through L) returning column 12.
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time.
If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs)
If done at 0830 hrs, Shift 2 (0801 to 1600hrs)
If done at 2030 hrs, Shift 3 (1601 to 2359 hrs)
I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities.
In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario:
Warranty Replacement = 20
Insurance Claim = 20
Billing Issues = 15
Retention Opportunities = 20
I need an excel function for this...
Say,
Cell A2= 8
Cell A3= 1
If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
I'm attempting to do the following:
A1 contains names of projects.
B1 contains variances from budgets, being positive or negative numbers.
C1 will contain results.
I need to know which of the B1 cells contain a number equal to or greater than 20%/-20% and output the names in A1 that match the criteria to C3.
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
When i have a formula standing at A3 fore example =A1-A2
and when i fill in a number like 100 at A1
And i fill in a number like 150 at A2 then at A3 where i had make my formula in there stands -50
Now i want the -50 not to disapear but instead there must be stand a 0(zero)
What is the formula i can use
i dont mind to put in at another cel like A4 for example
I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.
IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column
IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column
IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
I have a scorecard that looks something like this:
90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0
I need the "cell" to react accordingly and I'm lost.
how to have a cell equate to an autoshape based on the results of an "if" statement?
View 4 Replies View RelatedI would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
View 2 Replies View RelatedHow to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.
I don't want to include the formula in the sheet itself, just have that as the formula in the condition.
The formula will be along the lines of:
=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)
My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.
I have a formula sheet that uses an IF statement to determine if one columns data is bigger than another. Out of the 300 or so rows there are approx 20 that come back as yes (this is in column A) and the rest are blank
What I would like to do, is for the 20 or so rows, I would like to pull out (copy) columns B, J and L and put them into worksheet 2. Preferably without any gaps in the rows or columns.
I hope this is enough information, I am using MS excel version 2010 although I think the people who will ultimately be using it are on an earlier version.
I need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.
Column A
Date
1/2/2007 0:00
1/3/2007 0:00
1/10/2007 0:00
2/10/2007 0:00
2/10/2007 0:00
2/15/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
Column B
Invoice #
CM-0003881
CM-0003882
CM-0003883
CM-0003888
CM-0003928
CM-0003932
CM-0003933
CM-0003985
CM-0004007
CM-0004008
CM-0004009
CM-0004065
I have this formula in cell F3 that will add the dates through the end of January:
=SUMPRODUCT((A1:A1000>=DATEVALUE("1/1/2007"))*(A1:A1000
how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.
View 2 Replies View RelatedI have an Excel workbook with multiple sheets. In one sheet, there are many columns that automatically get hidden based on cells values (=1) in another sheet as I type. I use this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" And Target.Value = 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = True
Else
If Target.Address = "$C$7" And Target.Value <> 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = False
End If
End If
End Sub
Recently I had to change the second sheet that instead of manually entered values, formula results appear in cells. And the above code doesn't work anymore, columns are always stay unhidden. How can I achieve what I want? I need columns get hidden if formula results =1.
We receive payments in either USD or HNL. I want to be able to have a final price (last column) that puts all prices in USD. I have a reference cell on a separate worksheet (drop down inputs) that we will use to store the data for validations and the conversion rate. We use one conversion rate for all transactions for a fiscal year, so only need to update once a year. Not sure how to make this work.
View 2 Replies View RelatedThe following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A
0
0
0
0
0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
I have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
View 4 Replies View RelatedI created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View 2 Replies View RelatedI need to write a formula that will Display “Big Fish” if a customer is in the top 100 sales or ‘Not that great’ if the customer is not in the top 100 sales for the customer whose CUSTOMERID is equal to my ID.
An updated Spreadsheet can be found here
[url]
OR
[url]
formula for when i select from the drop-down boxes it returns a value that i have placed next to it on a separate sheet. I have attached an example of what i am trying to accomplish
View 3 Replies View Related