Multiple IF's And AND Inclusive Formula (all In One Cell) That Would Look At The Above Table And Depending Upon The Price Paid

Oct 24, 2008

.............................24............30............36
300014999..........9.00%.......11.00%.....12.00%
1500099999........9.50%.......11.50%.....12.50%
100000249999.....9.00%......11.00%.....13.00%

I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.

View 4 Replies


ADVERTISEMENT

Lookup Percentage Formula Depending On Price Entered In Cell?

Jan 28, 2013

I have the following table in excel:

Price: Percentage:
$150 10%
$100 12%
$50 14%

I'm trying to use the percentage depending on what price I enter in a cell. For example; if I enter $135 then 10% will be used, if I enter $65 then 12% will be used, and if I enter $47 then 14% will be used.

View 3 Replies View Related

How To Create Formula Which Finds Out Price Depending On Two Things

Mar 12, 2013

So I have this assignments. Its about rental services. I need to create a formula where the price is automatically found from a table depending on what drop down menus are selected. One menu is the rental name and the other is the season i.e. high or low, therefore each rental has two prices.

I know i can use Vlookup if there was one drop down menu but how to do the other. I was think it will interms of if statement but i dont know...

Here is the link to the worksheet: [URL] ...........

View 5 Replies View Related

Formula To Have One Cell Display Multiple Options Depending On Value In Another Box

Oct 1, 2013

I need to build a formula to have one cell display multiple options depending on a value in another box.

If Value is >x and <x display Y

Details:

Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:

If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.

View 2 Replies View Related

Formula To Get Inclusive Dates In Between Start And End Date

Jan 21, 2014

I have a table where I assigned Months (Jan-Dec), Current Month YTD (e.g. Nov YTD) and Dec YTD in the column field. Company Name and Year in rows.

For a particular company for example, Co.AAA. The Start Date is Oct 1, 2009 and the contract will end on Sep 30, 2014.

If my current reporting month is Nov 2013, From the table, I need to get the sum up the data that correspond from this dates inclusive in start and end date of Co.AAA.

Inclusive Dates are:
Dec YTD 2013
Dec YTD 2012
Dec YTD 2011
Dec YTD 2010
Dec 2009
Nov 2009
Oct 2009

If this is not possible, what other solution I can use to get the correct number?

View 9 Replies View Related

Formula To Compute Commissions Paid

Jun 9, 2014

I need to write a formula that will look at a number in a cell and compute an amount at 1% up to 55,000. Then anything above the 55,000 compute an amount at 3.25%. Then add the two together.

So a baseline of 55,000 at 1%. Then above the baseline 3.25%.

View 3 Replies View Related

Formula For How Much Interest Would Be Paid In A Given Timeframe

May 27, 2008

I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period.

I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row.

For example

Balance | Rate | Payment | Time Period | Interest Paid
$1,000 | 10.00%|$20.00 | 6 | $48.56

View 9 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

Formula For Range To Calculate Commission Paid In Month

Feb 26, 2014

I have attached a spreadsheet which shows my desired result which often works.

Range C2:C13 is my desired result...

The problem /criteria is...

For every 140 meters sold the sales person receives £140 commission.

Meters (range B2:B13) can be carried to future months.

I am looking for a formula for range C2:C13 which calulates if commission should be paid in this month or not and if so, how much.

View 5 Replies View Related

Creating A Mat Table Based On A Price Table

Dec 7, 2008

Part 1: Pricelist
The user presses 1 of 3 macro buttons to access the price list.
The buttons represent 1 of 3 parts of a construction project.
The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro,
depending on which button is pressed, hides all but the relevent quantity
col & total col.

Each job phase needs its own total & having it setup this way makes it
easier to enter new items into the table (only have to enter
the item once, rather than 3 times).

The first html shows a simplied example of the "price list" described above
(before & after the quantitiy & price cols are hidden).
The user adjust the quantity & the sheet calculates the price total.
Part 1: Material listThe material list is a list of items & quantities the user selected in the
quantity col of Price list. This list is to be printed & given to the workers.
It does not show price, only item & quantity.

This list, unlike pricelist, must be visiablly broken down by job phase.

I dont want the material list to be the price list repeated vertically
on a sheet 3 times. This would take up too much space & require new
items to be entered once in the pricelist & then 3 times in the mat list.

I would rather the mat list be on a seperate sheet from price list & have
a formula in cell matlistA1 that searches the phase 1 quantity col
(then phase2 & 3) for the first #>0.

When a #>0 is found, the formula
would return an item name into the cell on matlist. Then the formula
would run in matlistA2, & search for the next #>0.
(& so on until a complete mat list table, broken up into phases is created)

Sheet2

ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73
..............................

View 9 Replies View Related

Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date

Apr 21, 2006

I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.

Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!

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

Looking Up Items In Price Table

May 9, 2014

I have a price table with about 70,000 line items (some lines are duplicates).

There are about 12 columns each with different qualities such as item name, size, thickness, price and etc.

I've been building this table in excel and just importing it into Access for the time being.

When a customer sends me an order, I'd like to be able to look up the items in my price table (matching across multiple columns) and automatically return the price for each item.

So far, I've been using the following formula....

=LOOKUP(2,1/($A$1:$A$60000=D1),$B$1:$B$60000)

With A5:A60000 = price list concatenate so all columns in 1
D1 = item I'm searching for
B1:B60000 = prices for items

The only thing is that this is very labor intensive.

The only thing is my customers will typically submit an order and it is NOT in the same format as I need it to be.

So it's very labor intensive and I spend hours just editing their order in a spreadsheet so that it follows the format I need it to.

View 1 Replies View Related

Count Cells Depending Upon Multiple Cell Values

Aug 2, 2007

In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83,

Order No OP Posted Set Allowed Set
30761157 00100.731.500
3076115700100.500.000
3076115700100.600.000
3076461000100.000.000
3076461000101.050.500
3076524400100.000.000
3076524400100.550.500
3076639600100.000.000
3076639600101.180.500
3076810900100.000.000
3076862900100.000.000
3076862900100.000.000
3076862900100.000.000

View 9 Replies View Related

Cell Format Changes Depending On Formula

Jul 18, 2013

My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.

I want the answer displayed as 2.43 of 3. Here is the formula.

=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.

The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.

If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.

I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.

View 10 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

Formula Change Cell Color Depending On Priority

Aug 29, 2012

My colleagues are working on multiple projects at once. The projects got different priority, so I'm looking for a formula that change the cell color if a person is working on it, depending on the project's priority.

For example if John is working on project: East(pri.1), South(pri.2) and West(pri.3). On East he got 5 remaining hours (cell E8). Then i would like that cell to turn red. For South green and West red.

View 5 Replies View Related

Formula In A Cell Value Changes Depending On Drop Down Menu Selection

Aug 19, 2014

I have created a drop-down menu combobox of districts and depending on your choice of districts the list of Health Centers in each district changes. What I want to do is to protect the contents of the cells that contain the health centers. The problem is that when I lock the cells and I choose a district, the Health Center cells cannot change because the cells are locked. Is there anyway around it?

I have attached the sample (Please look at sheet 2. The column that I want to block is the column FOSA)

View 4 Replies View Related

Formula For Showing Data Depending On Cell Entry

May 18, 2007

I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.

View 2 Replies View Related

Formula / Conditional Format To Award Score From 0 To 4 Depending On Cell Value

Jul 24, 2014

I want to create a system based on the value of a cell appearing as a grade in the adjacent cell. The cell could contain any value from -10 to +10.

the criteria would be

value greater than +2 = 3
value between 0 and +2 = 2
value between -2 and zero =1
value below -2 =0

I can get a formula to work for a single instance but not multiple conditions.

View 3 Replies View Related

Copy Fill From Another Cell That Is Formatted To Change Depending On Its Formula

Dec 10, 2012

As seen from my attached file,

The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)

Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.

If today is on or after the target date and the actual date is not filled, the cell will turn red.

However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.

FormattingHelpExample.xls‎

View 2 Replies View Related

Days Between 2 Dates Inclusive

Jul 25, 2007

I have a simple formula to work out the number of dates between to dates
ie: =U6-T6

This works out the days between 9 may and the 31may as 22 days
but i want to show the correct number of days including the 9may so this would be 23 days.

I tried this =U6-T6+1 which was fine but all the empty cells now show the value 1 in them.

View 5 Replies View Related

Specific Formula Executed In Column Of Data Depending On Selected Value In Cell

Apr 4, 2013

I have a sheet with a country in it in A1 (validation list).

Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)

I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.

I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..

View 5 Replies View Related

Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies View Related

Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day

Jun 9, 2008

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies View Related

Output Comma Separated List Of Inclusive Years?

Jul 2, 2013

Given a list of beginning years and ending years, how can I create a column that gives the whole list of inclusive years separated by a comma?

Start Year End YearList of Years
1985 1995 1985,1986,1987,1988,1989,1990,1991,1992,1993,1994,1995
2000 2009 2000,2001,2002,2003,2004,2005,2006,2007,2008,2009

View 5 Replies View Related

Multiple Pictures In A Price List

Oct 6, 2008

Hi, I've got a price list with 2500 different lines which the boss has decided need a picture against each one (the guys a legend!!). Can anyone help me with a macro that will look up the code in Column A, then add .jpg on the end, and insert a picture into Column J?

View 11 Replies View Related

Lookups For An Item's Price From A Price List

Jan 4, 2010

The analysis basically has 2 data components to it:
The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.

The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.

What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:

1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)

2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)

3) Pull the price for this "closest quantity"

I have uploaded a worksheet showing the structure of that data.
[url]

Is there some VB code I need to do this, or can it just be a few simple formulas?

View 4 Replies View Related

Multiple % Discounts Off Base $ Price That Do Not Cascade?

Mar 3, 2014

Cell B23 contains an invoice base price in $'s formatted to 2 x decimal places Cell I4, I5,I6,I7,I8 all contain various percentage discounts formatted to 2 decimal places

I am trying to creat a formula in cell I23 that will subtract the discount of each of "I" range cells - lowest number to highest- in order. But each discount has to be subtracted from original invoice value in cell B23

Example:-
Cell B23=$1660.00
Cell I4=14.00%
Cell I5= 3.50%
Cell I6= 3.00%
Cell I7=1.50%
Cell I8=2.00%

I have tried using =B23*(1-I4)*(1-I5)*(1-I6)*(1-I7)*(1-I8) But this appears to be a cascading discount By my manual calculations answer should be $1261.60 But above calculation gives me $1289.94

View 1 Replies View Related

Formatting Multiple Vendor Price Lists?

Apr 20, 2011

We currently have a few hundred vendors we order from for a retail business. We receive price lists in excel format, but they all differ in how they are formatted, i.e. one may have a column header of "UPC" and the next may say "UPC ID" and they may be in different columns. We currently format a handfull of these price lists so they have the same headers so we can easily compare costs, margins, etc, but we don't have the resources to do many more than this.

View 6 Replies View Related







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