If Function To Return One Of 3 Results

Nov 22, 2007

I am looking for a formula that will yield the following: IFAH9>AI9 return the word Green, IFAH9=AI9 return the word Blue, IFAH9<AI9 return the word Yellow. If AI9 is blank stay blank.

View 3 Replies


ADVERTISEMENT

Get AND Function To Return Back Results Other Than True And False

Mar 5, 2014

Here's my formula:

=AND([@[Tenure (Yrs)]]>=5,[@[2016 Sales]]>=150000)

(Where should I enter high and low? Do I need an if formula nested into it?)

I'm trying to get it to read if the customer reads both criteria which is they've been there 5 years and over 150000 sales =
High if not Low.

View 4 Replies View Related

Single Formula/Function Return Results To Multiple Cells

Aug 26, 2006

I've got an If statement in Excel which features several VLookups - I need the IF statement to return differing results to 2 different cells, is this possible?

In plain English, I'm looking for something like

If(Vlookup(A2, B2:D5, 4, False), C6="y" And D7="ok", "False")

Obviously this is a very crude example

View 3 Replies View Related

Sum Cells And Return Results

Jun 25, 2013

I would like a formula to calculate the following:

If either one of U8 or AP8 are blank return blank (both need to have a number in)
If sum of U8 & AP8

View 1 Replies View Related

Check A Value And Return Results

May 5, 2008

I have a table that has 6 columns and 5 rows. I need to check a name (division) against the column heading, then and return a classification from the first column based on the score.

Below is an example of what I am using (I'm not sure how to post a screen shot):

DIV1 DIV2DIV3DIV4DIV5
A91.7689.4198.82100.82102.35
B111.43108.57120.00122.00124.29
C141.82138.18152.73154.73158.18
D195.00190.00210.00212.00217.50
E195.10190.10210.10212.10217.60

DivisionDIV3

Total Score 155.6

ClassificationC

View 9 Replies View Related

Formula Return The Same Results

Nov 11, 2009

The formula in Col G uses the value in col's B:E. I would like a formula in Col G that will use the 4-digit value in Col F ( same values as Col B:E) and return the same results.


******** ******************** ************************************************************************>Microsoft Excel - PLAY 4 EVE MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1F2G2F3G3F4G4F5G5F6G6=ABCDEFG111/10/0966666666Q211/09/0977667766DD311/10/0977767776T411/11/0972467246S511/12/0977667766DD611/13/0978667866DSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Lookup And Return Multiple Results

Mar 31, 2014

I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.

View 10 Replies View Related

Lookup Value And Return Multiple Results

May 4, 2014

I have been using this formula to do lookup and return values

"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"

I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.

Is there a way to stop it from changing the value once a value is entered in a corresponding cell?

Link to the original formula thread. [URL] ........

View 10 Replies View Related

Search Tabs And Return Results

Feb 2, 2010

I would like to summarize all "yes" respose contained in 15 tabs. See the attached.

I would like results to be returned to the "summary tab" when user answer "yes" in the signficant column in the "process activity 1 through 15" tabs. If the answer is yes, I would like to pull the process name, aspect and impact detail.

View 14 Replies View Related

COUNTIF - No Results Return Only Formula

May 23, 2014

I am trying to track the number of "F"s within a cell and I'm sure that I'm using the formula correctly -

=COUNTIF(I2:I29,"F").

When I use the insert function feature to walk through setting up the function step-by-step - I see the count once I type in the criteria - however, when I hit 'Ok' the count doesn't appear in the cell - the formula appears. I have cut and pasted the entire worksheet into a new spreadsheet, the problem remains.

View 9 Replies View Related

Search A Column & Return Many Results

Jul 8, 2008

I have a workbook with two worksheets.

On Sheet1 I have two columns 'Date' & 'Serial Number'

I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.

View 9 Replies View Related

Return Lookup Results Of All Occurences

Jan 30, 2008

I am using vlookup to return data from a range that could have the same name in column A.

There are 8 columns
Column 1 is Name
Column 2 is Sun
Column 3 is Mon
and so on.

The problem I have is that a name can appear in two places in column1 with relevant information in different columns:
Fred appears twice, on Wed he is on a course the rest of the week he is on leave. I need a C to be returned for Wed & A/l for the rest of the week.

View 9 Replies View Related

Lookup Formula To Return Multiple Results?

Dec 8, 2013

I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.

For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.

I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")

I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.

View 3 Replies View Related

Auto Filter Very Slow To Return Results

Feb 12, 2009

i have a work book of 12 sheets, the two main sheets 1+2 have 5000+ rows. by 30 columns. each row column b is a site number in ascending order(up to 4 for each site) so auto filter select = to site 5 about 10 seconds later i get the result of 3 sites try site 846 30 odd seconds later up pops the result. so i copied the sheet to a new work book and tried and it's nigh on instantaneous. next step
copied sheet paste special values into new sheet in same original workbook
and tried filter on that still slow as ever, thing is this book around 8meg and i cant for the life of me work out why ive cleared excessive formats and reset last cell on each sheet. there is a vlookup from sheet 1 to 2 for each site to return serial numbers. i have tried it in vba by turning autocal off/on but still no difference.

View 3 Replies View Related

Conditional Formatting To Return The Following Results If The Criteria Is Met

Jun 19, 2009

I have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:

If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue

The problem is when B3 is greater than 0 it does not change the cell colour to Blue.

B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue

See attached for spreadsheet with conditional formats

View 3 Replies View Related

MODE() Is There A Way To Return Bi-modal Or Multimodal Results

Jan 8, 2006

I'm working with lists of numbers that should return bi and multi-modal
results when analyzed.

Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?

View 10 Replies View Related

Can Index Or Match Return Multiple Results

Nov 27, 2006

I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!

I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status

plus a whole bunch of other columns of information.

On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.

I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.

Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.

View 12 Replies View Related

Return Text Results Based On 2 Criterion

Oct 6, 2008

I am attempting to create a formula that will, return text results based on 2 criterion.

This is what my sheet looks like

Name Job Full Time / Partime
Bob Admin Full Time
Barry Accounts Part Time
Chris Claims Full Time
Emma Claims Full Time

The formula I have for this is {=INDEX(A1:A4,MATCH(1,(((B1:B4)="Claims")*((C1:C4)="Full Time")),0))}

Which is fine and returns the name Chris, however as you can see from above, Emma also meets the criteria given however, I cannot get Emma to appear also. Is it possible to display more than one entry in a cell/s based on this formula, or am I missing something.

I would use a pivot table to do this, however I need the sheet to be completely automated.

View 8 Replies View Related

How To Return The Maximum Value In A Multiple Results LOOKUP

Dec 11, 2008

What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.

Please see attachment.

Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)

View 6 Replies View Related

Return Value Based On Results Of Conditional Formatting

Apr 19, 2012

I have a report that shows when our company has shipments covered by inventory (although you are not seeing the conditional formatting in the table). Columns K:Q represent shipment dates and shipment amounts. Columns D:I represent inventory with Column J being the total inventory. The conditional formatting works to highlight the cells in K:O of when shipments are covered by inventory. I need to be able to return the date (Columns K:Q) for an item (Column B) when a shipment is not covered by inventory.

For example Row 154: all shipments are covered by the amount of inventory. A shipment of 3 on 04/24 is more than covered the 105 pieces of inventory. But for Row 170, the shipment for 480 pieces on 04/23/12 does not have enough inventory to cover.

The result I need to return for row 170 is that 480 pieces are due on 04/23/12 that are not covered.

What formula would I used to return that value?

Excel 2010ABCDEFGHIJKLMNOPQ1LineITEMNEXT CUSTOMER PART WIP INVENTORYSTAGINGFINISHEDINSPECTDCYTRAILERTOTALPast4/18/20124/19/20124/20/20124/23/20124/24/20124/25/2012154Tabber105290325AA05290325AA-DC000010501053155Tabber1070025AC-EXP070025AC-

[Code]....

View 2 Replies View Related

Return Results Of Ping Test To Worksheet

Oct 18, 2012

I'm trying to work out some code that will take the contents of a cell (either alias or IP name), carry out a ping operation and return the result into the worksheet in the next columns to the right.

I've searched this site and found something similar that returns the result of the ping to a MSGBOX but this isn't quite what I want.

The endgame is to have a For/Next loop for a list of IP addresses in (say) A1:A10 and have the results pasted into columns B, C and so on, with each line of the ping result pasted into subsequent columns.

View 9 Replies View Related

Formula To Return Number Of Results From Table

Mar 12, 2013

I have the following table in a spreadsheet and want to return the number of rows where the Matched column is "F" and the Type column is "L".

Ref
Selection
Stock
Order
Matched
Type

[code].....

So in the example given the formula should return 1.

View 3 Replies View Related

Formula To Return Results Based On 2 Parameters?

Jul 5, 2013

1) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".

Example: Item # is in cell F12. (may or may not begin with "C" or "CE")

Item price is in cell J12.

Extended price (qty x price) is in cell K12.

In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.

If it does not begin with C or CE, then L12 needs to equal 100% of K12.

(The 2 parameters I referred to in the title were:

C, followed by a number
CE followed by a number )

2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).

View 2 Replies View Related

Serach A Column And Return Results Using Macro

Mar 13, 2007

Im trying to write a marco that will look in colum E and find criteria "a", then offset (2,1) and copy the activecell and the one below it to a1:a2, then return to where it was and offset (0,1) and then copy the active cell and the one below it to a4:a5.

after this is accomplished i need it to return to column E and start the process again for "critera b" paste the result to a7:a8 and a10:a11

I know folks usally post examples of the there code for other to take a peek at however I only know bits and pieces of the process and im not sure how to tie it all together...I was hoping someone could help me get a good start....thanks

View 9 Replies View Related

Return Results Based On Referenced Cell

Nov 21, 2006

I am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)

View 3 Replies View Related

Return Multiple Results Based On Criteria

Dec 1, 2006

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.

View 9 Replies View Related

Lookup All Occurrences & Return Results To 1 Cell

Jul 13, 2007

My task is that I have several documents exported in excel. The requirements in them were mapped to another document. The IDs were populated in a certain column but are now no longer needed. I have to convert those old Ids to the current ones. In my example I have populated Sheet one with the lookup table. In column A are the old IDs. In Column B are the new ones they map to. The old ones can map to several which is why there are more than one ID in some of the cells. In column D of Sheet 2 I have populated it with the old Ids. In Column A I'd like to have the lookup return the appropriate maps to the new Ids. For example for the first map in cell A2 it should return:

abc_18
abc_43
abc_6
abc_4
all in one cell.

Vlookup can return the first hit (abc_18 and abc_43 for cell D2), but ignores the other two IDs in cell D2. I was able to work around this combining the find, mid, and vlookup to return all values. The problem with that is that the formula gets monstrously big after just having it return 4. In the actual files it can be up to 30 IDs which would make that formula literally pages long since. I have pretty much no experience with macros, but it seems like the only solution since I have to send this off to various people so they can use it.

View 3 Replies View Related

Function Returns Value To Cell As Single - Function Is Defined To Return Double

May 14, 2013

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double

[Code]....

View 9 Replies View Related

Vlookup Data And Return Results On Multiple Entries

Feb 16, 2009

So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.

I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.

View 14 Replies View Related

Vlookup To Return Results Based On Different Sexes And Ages

Nov 4, 2008

I want vlookup to return results based on the *** and age of the person from tables in a separate sheet. For eg. if the person is "male" or "female" and if the person is "<=15", "16", or "17". I have been practicising with a formula that looks like this:

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved