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 am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.
File is attached.
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
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 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.
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.
I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
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.
I 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 RelatedI 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..............................
How 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...
In range B1:C5, I want to return the value in column B to column A if column C is empty as shown in the following table.
Coml A Coml B Coml C
S1S1
S3S2 89
S4S3
S4
S5 67
I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1
2 JLKNSTTP
3 85934942
4
5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
=if((3:3>8),offset(A3,-1,1,1))
i have a spreadsheet like the following
A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan
and so on...
So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View 5 Replies View RelatedI'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.
Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
I have a table with the following headers: Customers, Location, bill number, date of bill, number of days you have untill you must pay the bill, the rest are not important
I need to return a list of the bills from the last six months, in which the customer has been granted with days until he must pay the bill(there are some with no granted days).
The table headers are translated, they are not so long.
I need to do this until friday.
I would like Excel to return me a value based on many criterias in the same worksheet.
E.g
A
B
C
[Code].....
QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
I have a spreadsheet with several hundred line items that I basically want a summary of.
STRINGS CRITERIA OTHER
string1 criteria1 other2 <---disregard because of other2
string1 criteria2 other1
string1 criteria2 other1 <---disregard because it is a duplicate
string2 criteria3 other1
string2 criteria3 other1 <---disregard because it is a duplicate
string2 criteria2 other2 <---disregard because of other2
string3 criteria1 other1
string3 criteria1 other1 <---disregard because it is a duplicate
I need the formula to return the value in STRINGS & CRITERIA, but I do not want it listing duplicates.
-I need the summary list on a separate sheet
-STRINGS, CRITERIA, and OTHER are all strings
-STRINGS are in alphabetic order
-CRITERIA are listed in groups with regard to their STRING, but not necessarily alpha-order
-OTHER are not in any particular order, but there are only two choices for OTHER
-OTHER: if OTHER=other2, it should not be listed in the results
RESULT
STRINGS CRITERIA
string1 criteria2
string2 criteria3
string3 criteria1
Sheet 1
Column A = Dates
Row 1 = Filenames
I enter a Y in the intersecting cells of the Filenames & Dates to show which files were downloaded.
I need is a formula that will rearrange this layout onto Sheet 2 so that each ‘Y’ (downloaded) filename is inserted in a cell corresponding to the Date.
(see attached sample)
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
Please see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.
=MAX(INDEX($A$1:$C$40,MATCH(A2,$A$1:$C$40,0),3),1)
MakeModelSale Date
AudiA4 11-Jan-14
AudiA4 quattro 12-Jan-14
AudiA4 quattro3-Jan-14
AudiA5 Cabriolet 14-Jan-14
AudiA5 Cabriolet q15-Jan-14
AudiA4 16-Jan-14
AudiA4 quattro17-Jan-14
AudiA4 quattro18-Jan-14
AudiA6 quattro19-Jan-14
AudiA4 10-Jan-14
AudiA4 quattro09-Jan-14
AudiA4 quattro10-Jan-14
AudiA4 11-Jan-14
AudiA4 quattro11-Jan-14
AudiA4 quattro11-Jan-14
AudiA8L 11-Jan-14
AudiA8L 11-Jan-14