Perform Calculation Depending On Cell Value

Dec 14, 2012

I'm developing a financial spreadsheet.

I've got a cell "Taxable Income" and another cell "Tax on Taxable Income" in sheet2.

When I enter a value in "Taxable Income" I want "Tax on Taxable Income" to automatically calculate based on formulas in sheet1. I don't know how to get "Tax on Taxable Income" to determine which formula to apply from the different tax brackets.

For example:
If taxable income is <$18201, tax = $o
If taxable income is between $18201 and $37000, Tax on Taxable Income =0.19*Taxable Income
If taxable income is between $37001 and $80000, Tax on Taxable Income =3572+0.325*(Taxable Income - $37000)

The idea behind having formulas in a different sheet is so over time when tax rates/brackets change I only have to change the formulas on one sheet.

I know how to use basic AND, IF statements e.t.c but I can't seem to get the syntax right for this to work.

View 1 Replies


ADVERTISEMENT

Can A Cell Contain Text And Perform Calculation?

Nov 17, 2009

I would like to have a cell in which there is descriptive text and the results of a calculation. For example, in row 2, I want to state the current total number of items in inventory at the top of the sheet in the center of the used cells.

Inventory Total is: SUM(A4:A64,000) as of: Date()

I can't merge cells or manipulate the text in such a way to neatly bring the text and calculation together. And there are actually 2 calculations in the example above. One to SUM the items and one to add the date.

View 5 Replies View Related

How To Perform Calculation On Pivot

Dec 25, 2013

it is more than 5 days now that I try to perform calculation on pivot.

problem: I have one cell that I set to show values as "% of the column total" when try to perform the calculation through insert calculation field with other cell the calculation is not correct/ it seems like the "show values as "% of the column total"" disturbing the calculation resolve this thing

View 14 Replies View Related

Cell Lookup - Calculation Depending On Criteria Column?

Jun 29, 2013

Criteria
Column B

Constant
Variable0
Variable1
Variable2
Variable3

2
[Variable2/Constant]

5
123
43
45
76

[Code] ..

This is my table. What formula should I use so that Column B shows the calculation depending on the Criteria column?

View 9 Replies View Related

Using Function To Perform A Calculation If Certain Values Are Met, Otherwise Return 0

Jun 10, 2006

As an example, cell values as follows.

A1 = 1, 2 or 3, B1 = 2.5

Is it possible to say, if A1= 1 then make C1 equal to B1+4, otherwise return the value of 0?

View 6 Replies View Related

Option Button (active Control) Code To Perform A Calculation

Nov 18, 2008

is it possible to choose an option button which will then perform a calculation on a cell reference? I’m trying to deduct a percentage from a total when an option button is highlighted.

View 5 Replies View Related

% Calculation With Values Depending On Size..

Jan 12, 2010

There is a data range in the E + G columns. The content of the E column can be 5 different items with variable GROSS weights in the G column:

.........E..........G..........R...........S...........T
1.....Size.....Gross....%.........Net......Size
2.....200 g....138......56.52...20........45 g
3.....100 g.....84....................35......100 g
4......45 g......52....................60......200 g
5.....500 g....253...................124.....500 g
6.....Strip......15.......................7........Strip
7.....100 g.....90
8.....500 g....280
9......45 g......62
10........................

View 2 Replies View Related

Perform Task Only If Cell Has Value

Jul 29, 2013

I have a large spreadsheet set up with multiple rows, I have the formula -

=a5&"repeated text"&c5

this brings back 2 results

test repeated text test
repeated text

In the instance this is the latter with no combined cell vale, how would I mass delete them?

View 2 Replies View Related

F4 Key Does Not Perform Absolute Cell Reference

Mar 30, 2014

I am taking an online computer class and when i enter a formula (=c5/c11) and then press f4, to make the cell c11 absolute, this does not work. Instead it brings me the "Project" right side mini screen. Is there a way to program the f4 key to return absolute results?

View 5 Replies View Related

If Cell Is Blank And Data In H Is A Certain Value Then Perform VLookup

Feb 4, 2014

Possible to have two spreadsheets and then perform the following tasks

1. Check If the cell in column I from cell I4 and the spreadsheet varies in length is blank and if it is - see item 2

2. The data equals certain values i.e. CDE or CDE - QUO or CDE - PRO - see item 3

3. Perfom a VLOOKUP to another spreadsheet

If the routine stops at any of the items then move to the next cell

Data is on spreadsheet - Allocations.xls
Vlookup is performed on this spreadsheet - 001 - Allocations - Blocks

The Vlookup for example is: =VLOOKUP(A5,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536.9,FALSE)

View 3 Replies View Related

Formula That Will Look In Col A And Perform A Subtraction In Cell B1 Between 2 Value Dates

Jan 4, 2009

i need formula that will look in Col A and perform a subtraction in Cell b1 between 2 value dates 28/12/08 -12/12/08. Though I could have done this manually but was wondering if there is formula which will avoid duplicate dates. So the formula is set and when i copy new data in Col A daily the formula should be able to pick 2 dates regardless where the second date starts at...

View 9 Replies View Related

How Do I Perform An Array Multiplication If A Cell Contains A Specific Text String?

Feb 17, 2009

Sum every instance of [SPREAD]*[SIZE] where [NAME] occurs within a text string.

Please note, I'm trying to do this without creating a new column [SPREAD]*[SIZE].

View 11 Replies View Related

VBA Macro To Select Cells With Certain Value And Perform Action On Adjacent Cell

Mar 10, 2014

Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.

Attached File : LoopingVBA.xlsx‎

View 3 Replies View Related

Insert Formula Depending On Cells Depending Of Variables?

Jan 27, 2014

In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:

[Code] .....

All my variables are declared:

Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed

The error message I receive is the following: "Run-time error 13, Type mismatch"

I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.

View 3 Replies View Related

Loop: Macro To Go Through The Table And Preform An Operation In The Corresponding Cell In Column B Depending On The Value In Cell A

Mar 27, 2007

I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?

View 2 Replies View Related

Excel 2010 :: Change Cell Colour Depending On Date In Another Cell

Jan 9, 2013

I am trying to create a sheet in XL 2010.

In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.

Column B is when the weekly inspection is due.

Column C is when bi-weekly inspection is due.

Column D is when monthly inspection is due.

Column E is when 6 monthly inspection is due.

I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A

I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.

For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.

A1
B1
C1
D1
E1

Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due

January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3

View 3 Replies View Related

Formula To Change Cell Colour :: Shade A Cell Depending On Condition

Feb 9, 2007

Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))

View 3 Replies View Related

Changing Empty Cell Background Color Depending On Different Cell Value

Aug 7, 2014

I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..

I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.

I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.

View 6 Replies View Related

Excel 2010 :: Conditional Formatting Of Cell Depending On Value Of Another Cell?

May 9, 2013

I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.

View 9 Replies View Related

Inserting Values In Cell Depending On Content In Adjacent Cell

Mar 20, 2014

I have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.

View 3 Replies View Related

Changing Cell Colours Depending On Cell Content

Nov 19, 2008

What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.

View 6 Replies View Related

Replace Cell Content- Depending On Other Cell Data

Mar 6, 2009

I have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.

In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.

View 4 Replies View Related

Code Which Highlights A Cell Depending On Criteria Of Another Cell?

Mar 7, 2012

code which highlights a cell depending on the criteria of another cell.

Example.

In B1 I have the formula =NOW() (we all now what this means).

In B2 I have a completion date.

I would like A2 to fill with a specified colour.

Can this be done so that it works down the whole sheet, if the date is in B5 then only A5 will highlight so basically only the A cell on the same row will change depending on the date in B on the corresponding row?

View 4 Replies View Related

Move To A Cell Depending On Variable Active Cell

Jul 23, 2013

So, I just started checking Excel Macros. I'm working on a quite large data base where I need to search for a given part number and then move N columns to the right and display the text inside that cell on a message box.

Here's what I got 'til now:

Private Sub M0016216_Command_Button_Click()
Cells.Find(What:="M0016216", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Select

[Code] ........

View 3 Replies View Related

Make A Cell A Color Depending On A Number In A Different Cell?

Sep 26, 2006

how to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?

View 5 Replies View Related

Cell That Changes Depending On Month

Mar 4, 2009

I'm trying to get the pink cell in THIS sheet to match up with the correct MONTH'S TOTAL in the pink highlighted column in THIS sheet. i couldnt figure out how to explain it any clearer.

View 6 Replies View Related

Row Color Depending On Cell Value

Sep 3, 2009

I was wondering if it is possible to fill a row with a color depending on the value of a certain cell. Say, if the value of cell N5 is 0 then row 5 turns red.

View 3 Replies View Related

Use Same VBA Macro Depending On Cell Name

Jan 30, 2014

[Code] .....

But what I want to do now is to strikethrough to eliminate unwanted data when a particular option is selected from the drop down list. In this case ( If User choose "Mens" for Gender, and NOT "Pants", Then Strike though the Result "NIKE". This works for the template in column E only using this code.

[Code] ...............

I tried to modify this like

[Code] ...............

So that every time x changes, the condition changes, but it seems like I cant do, Range("NIKE_" & x).

Attached File : Test1-2ed.xlsm

View 2 Replies View Related

Hide Row Depending On Cell Value?

Mar 24, 2014

I would like to run a macro that searches column [AS] and hides any rows in that column that contain "0".

View 6 Replies View Related

Color After Depending On 1st Value In A Cell

Jul 3, 2009

I have another macro request.

I would like to ‘grey out’ a certain qty of cells depending on the 1st number in another cell. So 1st of all it would have to check in column B to see if there is any data and then check for the first number in that cell (there will be many numbers in the cells but we only need the 1st one (Ex.: B3 = “3 / 8 & 8”).

Then it should start counting from column D the found number of cells multiplied by 2.

Ex.: if Cell B3 contains “3 / 8 & 8” it will count 3 x 2 = 6 (3 cells x 2 = 6 cells)

Then, if after that count (now at I3), in the next cell over (J3) if the is data in the cell above (J2) then it should apply an Interior.ColorIndex of 15 for the next cells until there is no data in the cells above (if nothing after K2 the grey will be applied to cells J3 & K3 only).

View 14 Replies View Related







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