# Return Result Based On Time In Cell

Oct 2, 2007

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.

## Return Result Based On Text In Cell

Oct 26, 2007

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

## Formula: Return Result Based On Other Cell Values

Sep 27, 2006

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.

## Return 1 Result From Many Based On Multiple Condition Check Of Cell Value

May 21, 2008

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..............................

## Return 1,2 Or 3 Based On Result Of Formula

Mar 3, 2007

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

## Return Result Based On Criteria

Oct 27, 2006

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?

## Return Result Based On Multiple Criteria

Sep 19, 2009

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

## Return Result Based On Lookup Value Returned

Oct 17, 2006

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.

## Return Text Based On Calculation Result

Dec 18, 2007

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?

## Return Result Based On Percentage Being Within A Range

Jan 17, 2008

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.

## Return Values Based On Lookup Result

Feb 4, 2008

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.

## Display Result Based On Time Difference

Feb 15, 2008

I want to make a formula based on 2 times ex: 10:07:00 and 10:09:00. This formula should display "intime" if the diference between both is under 20 minutes, "outime" if the diference is above 20 minutes and it should display "error" if the time is under 0 minutes (this will only happen when someone makes a mistake typing in the time. For example 10:37:00 and 10:36:00)

## Return A Result IF A Certain Value Is Found In Another Cell

Jul 2, 2009

I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description:

On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found.

Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results: =IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))

## Return Cell Below VLOOKUP Result

May 30, 2006

After using a vlookup to find a value in a cell I want it to return the cell belows information.

## Return Result Of WorksheetFunction To Cell

Sep 14, 2007

I am editing macro formula... is it possible VB does calculation during code execution & return only the value to Cell. this way the formula calculation remain hidden & code will probably run faster..eg.

Range("D3").Select
ActiveCell.FormulaR1C1 = "= COUNTA(Data!C[-3])-1"
Range("D3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I need to calculate & keep only value not formula in excel sheet each time the macro runs. I am getting error

Worksheets("data").Range("D3").Value = Application.CountA(Data![C:C]) - 1

## Excel 2010 :: Return Result For Appearance Of Text String In Any Cell In Row?

Jan 30, 2014

I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.

I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.

## Return Time Intervals Based On X Minutes

Feb 20, 2008

I have a spreadsheet set up that has a series of timestamps and values. Please find a copy attached. I need to set up a macro that will allocate the timestamp in column C to an interval of time, e.g between 12:30 - 13:00.

To achieve this I tried to set the following code up in VBA, which would input a letter in a blank cell based on the time interval that the timestampin column C falls into, for example if the timestamp is > 12:30 and < 13:00 then "A"...the plan was to extend this concept to accomodate all of possible the time intervals I have.

Sub time_intervals()

Dim x As Integer
Dim lastrow As Long
Dim timeA As Date
Dim timeB As Date
Dim timeC As Date
Dim timeD As Date
Dim timeE As Date

The idea was that the timemaster value would be the timestamp in column C of my spreadsheet and the timeA,timeB,timeC etc would reference the timeintervals. However this does not seem to work as the value returned by my if statement is always incorrect - for example timemaster in cell C2 is less than timeA but the if statement always returns FALSE as opposed to "A"

## Change Result Cell Format Based On Input Cell Value

Mar 24, 2007

Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.

## Hide Cells Based On Another Cell Result

Jan 12, 2008

I would like to gray out or hide contents of a series or group of cells when a particular cell has a certain result. i.e. if cell A1 is <5 I want cell rows 5 thru 10 to be hidden or grayed out.

## Result Based On Existance: If Coulmn Contains A Anywhere Then The Result Should Be A

Dec 4, 2009

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.

## Formula For Cell Result Based On Other Cell Input

Sep 3, 2007

I'm trying to work out the way of having a cell give a result based on what has been input into two other cells.

Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.

## Add And Subtract Time And Get Result In Time Format

Dec 31, 2009

As mentioned below , in excel sheethow to add and subtract time value and get result in time format. As given in below sheet, suppose i want to calculate each day stoppage duration and finally total stoppge duration. Kindly let me know the farmula or function for the same with example. I'm using excel 2003.

STOPSTARTDURATION

10:4512:151:30
0:201:301:10
5:5012:256:35
19:3020:301:00
16:0517:051:00
15:5017:151:25
23:500:000:10
0:001:001:00
23:300:000:30
0:001:001:00
17:2018:301:10
3:504:501:00

## Fill Multiple Cells Based On The Result In One Cell?

May 31, 2014

When I enter barcode X in cell A1, I want B1 to say supplier and C1 to say product name and D1 to say package quantaty etc.

And if I enter barcode Y in cell A2, I want B2 to say different supplier, and so on, hopefully you get the picture.

## Calculate End Result Based On Cell Contents Of 3 Columns

Jan 13, 2014

I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F

For example:

Column A Column B Column C Column D Column E Column F Column G (Results)
Royal Mail 1 100 2.8 2.3 1.2
Royal Mail 2 100 2.9 2.4 1.3
Royal Mail 3 100 3.0 2.5 1.4
DPD 5 200 4.5 2.8 1.5
DPOST 1 100 1.2 3.2 1.7

I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]

Here are an example of what I have tried, amongst many...

=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.

## Replacing Contents Of A Cell With Another Based On Search Result?

Feb 24, 2014

My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.

I'll try to be as clear as possible:

tab1 has relevant cells "a" and "b", same row

tab2 has 3 relevant cells "c", "d", and "e", all on the same row

All cells are text values - some digits, but should be treated as a regular String.

The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.

## Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell

Aug 3, 2009

I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:

Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.

i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow

However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).

## Display Cell Address Based On Result Of Combobox

Jan 29, 2013

I have a combobox that returns me the names that are in a spreadsheet.

I need a return label, the index (address of that cell that the combobox returned), how do I do that?

## Run Macro Based On Result Of IF Function/Formula In Cell

Sep 14, 2006

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.

## Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result

May 24, 2008

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.

## Excel 2010 :: VBA To Apply Cell Protection Based On Conditional Formatting Result

Jan 27, 2012

I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.

I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.

I am using Excel 2010 and Vista.