# Formula To Calculate Result Based On Range

Dec 13, 2012I 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.

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.

ADVERTISEMENT

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

Firstly its probably best you download the file from below and open it up.

http://www.mediafire.com/?un3smhmdyzt

I need a formula to put in cells in column S on sheet March.

It needs to match up the correct price from sheet 'Container Price List'.

The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'.

So for example, if you look at row 27 in sheet 'March' the price displayed would be £80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is £80.39.

If for example the exchange charge displayed £0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.

I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.

I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:

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 have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.

| ID X

| +------+------+------+--->

| | 1999 | 2000 | 2001 |

+------+------+------+------+--->

| Jan | $100 | $250 | $300 |

+------+------+------+------+--->

| Feb | $200 | $300 | $200 |

+------+------+------+------+--->

| Mar | $300 | $250 | $100 |

+------+------+------+------+--->

| Sum | $600 | $800 | $600 |

| +------+------+------+--->

| ID Y

| +------+------+------+--->

| | 1999 | 2000 | 2001 |

+------+------+------+------+--->

| Jan | $100 | $250 | $300 |

+------+------+------+------+--->

| Feb | $200 | $300 | $200 |

+------+------+------+------+--->

| Mar | $300 | $250 | $100 |

+------+------+------+------+--->

| Sum | $600 | $800 | $600 |

| +------+------+------+--->

Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:

>= First Day of the Month

<= Last Day of the Month

= ID #

The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?

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 array formula {=row(indirect("1:5")} which is result to {1;2;3;4;5} and it look like a range.

And i want to insert this into formula COUNTIF at the range section

i'd try it, but it didnt worked.

(its look like insert array formula into range section of a formula)

I have a problem with a calculation i am trying to do. I have to calculate holiday allocation for staff but i my company also offers length of service bonus days. After 5 years you get 1 extra day, after 10 years you get 2, after 15 you get 3 and so on. In my sheet i have a formula based on their start date that tells me their length of service in the following format: 10 years, 2 months.

I have put an IF Statement in for each of the different milestone criteria but the result is not coming back so i know i am missing something. Is there something wrong with the formula itself or is there a better one that will allow me to put the start and end of the range i want it to look at? For example can i tell it to say if between 5 years and 9 years 11 months put 1, if between 10 years and 14 years 11 months put 2 etc in column H.

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 RelatedI have Cell C63 in Worksheet 1 which is set up to say either "YES" or "NO" depending on whether a value on a different worksheet exceeds a value in an adjacent cell or not (also on that worksheet). The formula I am using is simply:

=IF('4. NORMALITY ASSESSMENT'!AC55>'4. NORMALITY ASSESSMENT'!AA55,"YES", "NO")

Essentially I don't want anything displayed if there are no data in the range C4:C54 on Worksheet 1. I thought I need a nested IF function but I couldn't get this to work.

Dim myDynRange As Range

Dim myLastRow As Integer

Range("H:H").Select

myLastRow = Range("H:H"). CurrentRegion.Rows.Count

myFirstCell = Cells(Counter, 8).Address

myLastCell = Cells(myLastRow, 8).Address

myDynRange = myFirstCell & myLastCell

im having a problem with the last line

myDynRange = myFirstCell & myLastCell

my eventual aim is to do an average on this column with a line like

ActiveCell.Offset(Counter, 1).Value = Application.WorksheetFunction.average(myDynRange)

the reason why my range declaration is so strage is because i was trying to change the dimensions of the range with an offset function because i had many ranges to average but the offset function didnt want to work. this way goes like this....................

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

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

Is there a way I can sum the result of a formula on a range of cells that meet a criteria? For example, I need to sum the difference of only the cells that are >46. (a1-46)+(b1-46)+(c1-46)...+(g1-46).

In this case the result I'm looking for is on row 2:

Is this possible to calculate in 1 cell only (h1)?

A

B

C

D

E

F

G

H

1

44.2

48.6

47.5

0.0

42.3

44.6

49.5

??

2

2.6

1.5

3.5

7.6

I'm using Excel 2007.

I have a field with a "YES"/"NO" drop-down box. There are three fields below which have some default formatting (thick purple dot-dot-dash borders). When the drop-down field is "YES", those three fields should change borders to a single thin black bottom border line.

OK... so that's the setup. And I should note here that it does in fact format properly. However, the problem I'm having is that the screen does not update / repaint with the new formatting. The thick purple dot-dot-dash borders remain, for the most part.

If I page down, then page back up, the new formatting appears just fine. When I switch from "YES" to "NO", again, the formatting changes, but the screen does not update/repaint so the only way to see the new formatting is to again... page up and then page down again.

I'm wondering if anyone knows a way to force the screen to repaint/update so that my users will see the new formatting without having to page up/down.

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

I have a query on retrieving the result from a set of range from other sheet.

Query: In the Sheet1 Column B has to take first two words from the

Column A & lookup the matching results from Sheet 2.

(The search range should be the first two words of each cell)

Result: The complete sentance in the Cells (First Two words) should get placed in the Sheet1 Column B

Example: The Expected result should be as mentioned below. (Blue in color should be the results)

Sheet1A BC1NameResults2abc def fkfeabc def words3ghi jkl kikgN/A4mno pqr stu lkasmno pqr must work5

Don't give my book don't give up6vwx yzyvwx yzy not7xwv uts rqpxwv uts yes8omn lkjN/A9ihg fed cbaN/A

Sheet2A BC1Lookup Data2abc def words3xxxghi jkl4yes you have to5don't give up6no you will not7mno pqr must work8vwx yzy not9Excel work10xwv uts yes11omn mre lkj12ihg not fed 13not work14

This is my query

I'm stuck using Excel 2003 to auto-populate a cell.

I have a range of dates in five consecutive columns called:

Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5

I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.

Each phase is consecutive to the next so will always be filled in from 1 to 5.

I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.

I've tried nesting some ISBLANK functions without any luck.

I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below.

week39

ABCDEF45939SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046039SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046139SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046239SCHTERR-WIEDEMANNDCCDIST-MUSCHANY0463 SCH Total 14042.2146439SCITERR-SULLIVANDCCDIST-MUSCHANY15587.1546539SCITERR-SULLIVANDCCDIST-MUSCHANY7217.2246639SCITERR-SULLIVANDCCDIST-MUSCHANY5316.6246739SCITERR-SULLIVANDCCDIST-MUSCHANY2720.6946839SCITERR-SULLIVANDCCDIST-MUSCHANY520.446939SCITERR-SULLIVANDCCDIST-MUSCHANY195.7147039SCITERR-SULLIVANDCCDIST-MUSCHANY047139SCITERR-SULLIVANDCCDIST-MUSCHANY0472 SCI Total 31557.7947339SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY4190.7147439SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3219.7647539SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3187.147639SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2209.3747739SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2132.7547839SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2034.56

Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471)

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

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 need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:

KEYPerformance

2010,000

2120,000

2230,000

2340,000

I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:

KEYMin FeeFee 1Fee 2Fee 3Fee 4

201000.10.20.30.4

211000.10.20.30.4

221000.10.20.30.4

231000.10.20.30.4

For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:

Fee Range 10-9999

Fee Range 210000-19999

Fee Range 320000-29999

Fee Range 430000-99999999

So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.

How can this be combined into a lookup/range/low-high formula to extract the correct values????

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