# Compound IF Functions

Aug 22, 2006See attached spread sheet as its a difficult one to describe

We are looking to apply labels to a cell depending upon criteria, the spread sheet contains the scenarios.

See attached spread sheet as its a difficult one to describe

We are looking to apply labels to a cell depending upon criteria, the spread sheet contains the scenarios.

ADVERTISEMENT

Have a look at the attache example. I have inventory items which have a fixed value. I have several quantity columns to reflect various inventory positions. (OnOrder, WIP, ATP, ATS, OnHand, PAB as at date()...)

At the top of the sheet I need to show sum of quantities and sum of values. In order to compute the values currently I need to hide columns to the extreme right to do the math on a row by row level and then sum the rows and copy the the value to another cell. In this case the cells with yellow background.

Is there a way to be able to write a formula that would return the sum of the qty multiplied by the value without adding an additional column. I would need to function like with filters as well (like =subtotal())

I have a column of years and a column of numbers representing annual

amounts placed in a savings account for the year. I would like to calculate

the balance of interest earned added to the balance of the account and then

calculate the interest earned on the accumulating amounts each year. The

results would be the account balance displayed in an adjoining column. So

far, I have not found a worksheet function for that. Could someone point me

in the right direction? Perhaps there should be several columns of data?

I am trying to create a calculator based on a worksheet I have. I do not know how to write a formula for simple interest calculations and for compound interest calculations.

View 4 Replies View RelatedI have selected a custom autofilter for one column of my data. After that, I want to use the top 10 autofilter in another column, but this appears to filter the top 10 overall, not the top 10 of the custom-filtered records. My data is transformer sizes.

First I custom filter the secondary voltage column to get only distribution transformers (less than or equal to 480). Now I want to know the 10 largest distribution transformers, but the top 10 filter on the size column makes all the records disappear. I believe it is selecting the top 10 overall, and it happens that none of the top 10 have secondary voltage less than or equal to 480. (When I change the custom voltage filter, I can get anywhere between 0 and 10 records for the top 10 by size.)

This seems to be an aspect of Excel, not an error. So far the workarounds I can see are:

1. paste the custom filtered data into a new sheet and then do the top 10

2. use the custom filter, sort descending by size, and count 10--but this problem was first brought up by someone doing the top 100, so this is not preferable.

Does anyone know a better solution that will make it easier to manipulate this data frequently?

Formula to calculate a daily compound interest based on the higher rate of the two rates for the first 5 years, then after 5 years the calculation would only be based solely on the blocked rate.

View 4 Replies View RelatedI am trying to work out a formula to calculate compounded interest rates.

I have a table that is 24 columns wide (months).

1 row that will have amounts input in to the columns (these will be different amounts)

I want to have a row along the bottom that calulates the compounded interest at a fixed interest rate on the total amount that is in the first row.

I have managed to do this using a table but surely there is a formula for this as the table can become very troublesome if the input amounts change.

Ive added an image (attached) : excel.jpgâ€Ž

I am trying to calculate the compound annual growth and my starting point is a negative number. The example is that I have (273,000) for the YE 2003 and have Growth to the amount of +767,000 at YE 2006. The formula I have been using for other calculations where the starting point is positive is =POWER(J23/C23,1/O$1)-1. Where J23 = a positive amount and C23 = a positive number. This formula works fine, but when C23 = a negative number the formula does not work and the % does not make sense.

View 6 Replies View RelatedI am trying to set up my excel to calculate daily compound interest.

The amount is 10,000 at 0.75% per day for 6 months.

I have tried several different things with no success -

I am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula

r = n * nt root (A/P-1)

where;

r = the effective interest rate

n = the number of times interest is added per year

t = the total number of years

A = the current value

P = the original value

The 2 problems I face are;

1. Confirming this formula would provide the correct answer (need maths expert here) &

2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel

I am looking for a formula which will compute interest earned (not ending account balance) for the following scenario:

1) Set $ amount (say 19250) is deposited on January 1.

2) Annual interest rate is .95%

3) Interest is compounded daily

4) Fixed withdrawals of $1750 are taken each and every month on the first day of each month

What is the total interest earned on the account? Basically, it is a issue where each month diminishes the "pool" of money earning interest. I get what's going on conceptually, but can't figure out which Excel function to use.

Im trying to work out the formulae or fuction that will work out the monthly compound rate of a loan.

The loan details are £140,000 at 7.55% APR for 20 years.

I would like to have an excel table that has 240 usable lines going horizontal (to the right) and vertical (down) from the starting number.

I need it to compound interest in both directions

240 is the total number of days per year I am trying to track

There should be adjustable starting amounts and compound interest

Be able to adjust the entered amount anywhere along the time line and it recalculate the amount from there on...

I am looking to calculate compound interest over a period of 10 years.

I am looking at putting a lump sum in at the beginning and contribute in monthly installments for the entire 10 years. How would I go about this.. I know there are formulas there, however I'm not a financial person at all..

I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:

Principal: $120 (user input value)

Duration: 9 (user input value in days, compounding daily)

%Intertest: 4% (user selected value, either 2% or 4%)

Max interest you can earn: $6 (fixed)

Max interest generating money: $150 (variable dependant on %interest, = $150 or $300)

Response/Answer is final value. I don't need the daily results like the example.

Result would be: $170.84

$124.80 (4.80 interest)

$129.79 (4.99)

$134.98 (5.19)

$140.38 (5.40)

$146.00 (5.62)

$151.84 (5.84)

$157.84 (6.00 reached the max interest level)

$163.84 (6.00)

$170.84 (6.00)

my equations I have so far only do one (below 150 total) or the other (above) but not both. and its just a regular formula: =IF(P<M,IF(P*I^D<M+1,P*I^D,"over limit"),P+6*D)........................

I've found this calc but it doesn't compare to my particular scenario/goals: http://dinkytown.net/java/InvestmentDebt.html. The major difference is that this dinkytown calc requires a new loan to be put in place and I am not in a position to refinance my current mortgage. My plan is to pay an extra $267 (b3) to the mtg (b4). It will take 128 months (b6) to pay off the mortgage. After that point, I need to calculate what the newly freed capital (b5) would do if I put in an account (i.e. a CD or a simple US savings account) that had an Annual Percentage Yield (APY) of 1, 2, and 3% for 20, 15, 10 and 5 years. I thought that I had the right formula in place for cells b7:b8 should the account earn a 0% rate of return, but I think it's faulty since it gives me a negative number for 10 and 5 year accumulation periods.

View 2 Replies View RelatedI have a column of 30 cells, each showing retirement dollars earned based on years of service (cell 1 = year 1, etc) and salary. Here is the predicament: A new hire is brought in with 10 years service, stays 5 and then quits. How do I change only cells 10-14 reflecting his/her salary and retirement dollars? Remember, cell 10 is the first year for the compound interest, cell 11 the second year, etc. I currently use the following formula for cell 10 (compounded salary from cell 9 * (1+.05)^1). Cell 11 is (compounded salary from cell 10 * (1.05)^1.

View 5 Replies View RelatedI am aware of the following topic in the VBA Help file:

"Using Microsoft Excel Worksheet Functions in Visual Basic

You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."

And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)

However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.

The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.

r = 100((Y/X)^(1/n))-1)

So for X = 200, Y = 750, n = 10, we have

r = 100((750/200)^(0.1))-1) = 14.1309%

I want to calculate the following...

If Cell A>40, Then the result should be ((Cell A-40)*23.25)

If it is not >40, Return 0.00

This is for a timesheet / overtime.

Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:

View 4 Replies View Relatedi have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.

View 7 Replies View RelatedSo I have say 1-4 columns that may have an A to describe a student as absent. In the 5th column I'd like it to add all the A's and return a value of 1-4 or if possible... a result that says 4A etc. The column might have another letter in it so i only need to add it up if it has an A in it.

To visualize, i just want to add up the students that have not attended a class.

I wish to set 2 different cases (good and normal sales) for a list of data.

During good sales

- fruits can be sold within 4 hours

- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)

- < 2 fruits per type

Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014

For normal sales

- fruits only able to sell of the next day

- Sell of on non-peak season

- > 2 fruits per type

Sample file attached.

I am trying to write a formula that will satisfy the following:

C1 and G1 are number values.

IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.

I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.

View 4 Replies View RelatedCurrently, all of our data in Excel are linked by using Excel Functions between workbooks and some of the files are so large that it may take 10 minutes to open and copy and paste a cell may take three minutes (which is ridiculous slow).

Pardon my ignorance, my understand of Excel VBA/Marco is about automation. Even i use marco to run the excel, i still need formula in the cell in order to perform the task, right?

Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:

= left(worksheet name! row(1) column.range("startno"),2

I have a cell (D19) that I want to be automated to return a value based on a formula (it will equal Cell D15). But I also want to have the ability to allow the user to override this and enter a manual value if needed. My idea on how to do this is having a check box with a macro where if it is checked it will unlock the cell and clear out the formula and when checked it will reenter the formula and lock the cell.

Also same form - I need a check box that will check all applicable check boxes (all boxes under Deductions to be taken). Can anyone assist me in setting up these check boxes so that my functions will work, or if there is a "better" way of providing these functions please let me know.

The file I am working with is available here

(1) I need to work out the value of 2 years worth of interest on a loan - details would be

Loan = 30,180.00

interest = 14.90% pa (calculated daily)

term of loan would be 60 months

I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...

the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)

The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...

(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...

what I would like to be able to do is

(a) insert the actual payment dates

(b) if no payment is recorded for that period, automatically insert an overdue fee

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