# Reverse Calculation Of Formula

Oct 9, 2007how can i create a formula by 3 variable?( power form: y=(x^a)*(v^b)*(g^k)*p that y,x,v,g are known and other parameter are unknown.)

View 2 Replieshow can i create a formula by 3 variable?( power form: y=(x^a)*(v^b)*(g^k)*p that y,x,v,g are known and other parameter are unknown.)

View 2 RepliesADVERTISEMENT

I came across the solution to this post - [URL] ..... - and was just wondering how to adjust the formula so that it calculates the correct tax starting from the net amount.

Say for example, an employee is paid a fixed, after-tax salary amount, and the employer is responsible for covering the tax - the salary payment needs to be "grossed-up" to calculate the correct amount of tax.

With 'tax brackets' as follows:

Taxable incomeTax on income (2011 – 2012)Tax rate

$0 – $6,000Nil0%

$6,001 – $37,00015c for each $1 over $6,00015%

$37,001 – $80,000$4,650 plus 30c for each $1 over $37,00030%

$80,001 – $180,000$17,550 plus 37c for each $1 over $80,00037%

Over $180,000$54,550 plus 45c for each $1 over $180,00045%

The formula to calculate tax, starting from the gross amount, is:

[Code] ......

in cell a1 i have 34

i have a formula

INDEX($A$20:$A$40,MAX(IF($E$20:$G$40=a1,ROW($E$20:$G$40)))))

is there any way i can make this formula look for

34 and the reverse of itself which is 43 also

know while doing paste special whether we can copy the data in reverse manner instead of regular pattern. Here, I would like to explain my query in detail.

As you all aware that we use paste special command for multipurpose requirements such as breaking the links/transpose etc. But, here I need to copy the data from last row figure as first in the same context first one as final.

Ex: if Data is 123456 and if we paste the data by using paste special, data will be same may be in in row /column. Here, I wanted to copy the data as 654311 which means reverse. Hence, kindly guide me whether is there any command/formula for doing the same.

creating a formula to rank certain cells in reverse order.

Example

Cell Total Rank

B1 = 2456 3

B15 = 2369 2

B30 = 2547 5

B45 = 2141 1

B60 = 2471 4

Rank every 15 cells basically in reverse order.

I am using Excel 2003. Looking for a way to find out whether some data I want to delete in a sheet is used by a formula somewhere else within the file. Is there a way to check that? In other words, I have the number 500 in A1, and I want to delete it, but I don't know whether it will impact a calculation on another sheet that involves that value in A1.

View 4 Replies View RelatedI am trying to write a formula to reverse data in a cell. Basically I am converting a number to hex then I want to take that hex string and reverse it. So it would be something like this my original number is 400001001 my hex number would be 17D787E9 because I am only allowing it to show 8 characters. I want to reverse the 17D787E9 to read 9E787D71.

My question is: How can I reverse that cell? I have searched google and this forum and can't seem to figure it out. I am sure I could do it in VB but I don't know any VB code.

I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...

however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!

one such formula is:

=IF(AND(AC11"No Calc",AC11>=1,AC11

I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.

I've got a formula

=IF(COUNTA(A1)=1,TODAY(),"")

When something is typed in Cell A1 the cell with the formula will input todays date.

Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.

I put my margin in G6 of the data sheet and the cost are the numbers on the Mail Rates sheet.

cost/(1-G6) and then round result up to next .25

=VLOOKUP(Calculator!E6,'Mail Rates'!A2:D32,Calculator!D6+1,FALSE)

I am using the vlookup above to bring the mail rates to a calculator. I want to add a margin to the mail rates before they reach the calculator.

The margin amount is in G6 of the "data" sheet and the margin formula is "mail rate"/(1-G6)

I would then like to round the result up to the next .25 dollar

Looking for a formula that can account for a minimum value.

The calculation is as follows 1.22*(x)+8=(y)

(y) must be greater than or equal to 20.

I am trying to calculate the present value of a terminal period in Excel. The manual calculation and excel PV function are off by about $98,000. Both calculations are using the same capitalization rate and terminal life. My PV Excel formula is as follows: =PV(discount rate-growth rate,remaining term (years),-terminal cash flow,,0)*present value factor in last year of cash flow) What is causing the difference in values? Is there something in the Excel formula that is causing the difference?

View 1 Replies View RelatedI have data regarding the percentage of QC for the team.

I need to find out the individual's average QC scores from the list already available.

The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage

is a formula to work out how many paid sick days an employee is entitled to based on their employment start date

Up to 6 months service the entitlement is 5 days

After 6 months it is 20

After 1 year 40

After 2 years 60

After 3 years 80

I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details....

The formula currently being used is... =IF(G50=E50, H50-F50+(H50

I am putting together a formula that will be able to choose the calculation. There is a couple of choices with the spreadsheet registry, non registry, and interfile. They have different standards per hour 56, 40 and so on. What I have so far is not working. It is =volume/(time*standard),Volume/(time*standard) with each standard being different to bring the correct percentage. How can I put the two to three formulas in one cell

View 2 Replies View RelatedI am getting a multiplication error in excel when I multiply 1796 X 156 the answer should be 280,176. However, I am getting an answer of 280,187 any suggestions? I am working with mutiple cells ect.

View 9 Replies View RelatedI have a simple calculation - Say 0-6. What i need to do is, if the value is <7 place a 10 in the cell, or if the the value is <12 place a 5 in the cell, or,

lastly if the value is <21.5 place a 0 in the cell. Is this at all possible - as i can only do it with conditional formatting using colours at present

I have attached a time card that needs a formula to calculate the time for the high lighted areas.

In+out-in+out= total hours.

I'm trying to use the if condition formula however it doesn't give me the calculation?

=if(A2<20%,"B2/.80", A2>20%,"c2*.02)

My division and multiplying functions do not work or it doesn't calculate for me?

if i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

$0 - $10,000 - 60% commission

$10,001 - $15,000 - 65% commission

$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%.

ps.. my excel sheet is set up as follows:

Rows a-g (stuff that is irrelivant)

row h, gross fees

row i, commission (in dollars)

There has to be a way to make this more simple. I want to add upp a simple calculation more or less in infinity =SUM(($D$1*D6)+($D$1*E6)+($D$1*F6)+($D$1*G6)+($D$1*H6)+($D$1*I6)+($D$1*J6)+($D$1*K6) .. etc etc..

View 3 Replies View Related1. In neighborhoods that have zero units in a given price range I have it to display "-" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "-". How do I get excel to glance over "-" and flag it for no calculation?

2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly.

For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision.

e.g.

Grand Peninsula=B5/N3

Meadow Glen(Mansfield)=B6/N3

...etc

Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.

I'm trying to do this:

Column A, B C

A has lets say 100 elements with certain ratings.

B has has a,b or c for each rating from column A.

C returns a for each rating from column A.

I search for a rating with this formula:

= INDEX(range,MATCH(rating;range,1,1,1))

Now I have the relative position of the rating in that range. Now I want to search for "b" in column B that reflects that rating. For instance:

Columns:

10 a 1

20 c 1

30 b 1

40 c 2

...

Search rating = 40 so C would be the answer.

But in that range A1:B4 I want to search for the position of the last b from column B

I tried this but I can't calculate the ranges:

match("b";b1:b1+x,0))

x= INDEX(a1:a100,MATCH(40;a1:a100,1,1,1))

I've tried an if statement with AND function with no luck either.

my spreadsheet looks like this:

A1 = "One", B1 = 17/08/2013, C1 = 1000.00

A2 = "Two", B2 = 16/08/2013, C2 = 500.00

A3 = "One", B3 = 17/07/2013, C3 = 1500.00

A4 = "Two", B4 = 13/07/2013, C4 = 400.00

A5 = "Two", B5 = 14/07/2013, C5 = 300.00

"One" represents the total that needs to be paid and "Two" represents the payments.

What I want to do is recognise when "One" is present and deduct "two" from that amount, but the whole spreadsheet is never the same (the data is being pasted from somewhere else) so it's really, really difficult to do that.

So I want to do =1000-500 (for that period, so in D2 it should say 500) and then =1500-400-300 (for the other period so in D5 it should say 800).

NB: Any payment ("two") that comes in after the 17th of the month will be carried forward to the next months "one".

I have a very basic formula, and I'd like it to ignore a blank cell. The sheet has cells A1, B1, and C1. I want the formula to subtract A1 from B1, and display the difference in C1. B1-A1=C1

Currently, if A1 has a value (it always has a value in that column) and B1 does not, C1 shows the negative value of A1. Ex: A1= 100, B1= blank. [blank cell] - 100 = -100

I want C1 to show nothing unless there's a value in B1.

How do I construct the formula so that it doesn't do the calculation for blanks?

Attempting to calculate assignment hours based on three fields -

A - Date

B - Txt

c- Txt

D - Time [Start]

E - Time [END]

F - Number [Off]

G - Number [Travel]

H- Number (Calculated Total Project Hours)

I HAVE THE CALCULATION WORKING WITH THIS FORMULA

=(E5-D5)*1440/60-F5+G5

My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.

I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula.

=IF(K23="","",IF(AND(K23=E23,ABS(B23-AVERAGE(F23:J23))>B20),B23-ABS(AVERAGE(F23:J23)-B20),

IF(AND(K23=F23,ABS(B23-AVERAGE(E23,G23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,G23,H23,I23,J23)-B20),

IF(AND(K23=G23,ABS(B23-AVERAGE(E23,F23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,H23,I23,J23)-B20),

IF(AND(K23=H23,ABS(B23-AVERAGE(E23,F23,G23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,I23,J23)-B20),

IF(AND(K23=I23,ABS(B23-AVERAGE(E23,F23,G23,H23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,H23,J23)-B20),

IF(AND(K23=J23,ABS(B23-AVERAGE(E23:I23))>B20),B23-ABS(AVERAGE(E23:I23)-B20),0))))))))

I need to locate/write a formula that can calculate the declining balance on revolving interest loans, such as a credit card. The formula needs to calculate the number of remaining monthly payments based on a stated interest rate and payment amount (i.e., present balance $2000, annual interest rate 18.99%, payment $60 per month). And then, if possible, the formula also needs to translate the answer into a definite Month and Year going forward from today's date (or calculated in another cell of the worksheet for display).

In essence the formula would begin as

(2000 + (2000 * (.1899/365)) * 30) - 60 =

then repeat the calculation using the answer above, and so on, until the original principal amount had declined to $0, and finally count the number of months it do to get there. This is essentially a mortgage type of calculation.

I know I could write the formula repeatdly one month at a time across a few hundred/thousand cells, but there must be a more economical way to do so.

I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

$0 - $10,000 - 60% commission

$10,001 - $15,000 - 65% commission

$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated..

ps.. my excel sheet is set up as follows:

Rows a-g (stuff that is irrelivant)

row h, gross fees

row i, commission (in dollars)

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