Reverse Calculation Of Formula
how 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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Reverse Data In A Cell / Reverse Cell Contents
I 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.
View Replies!
View Related
Formula's Keep Disappearing Formula Does The Calculation And Then Disappears
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
View Replies!
View Related
Formula For Deleting A Formula When The Formula Has Done Its Calculation
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.
View Replies!
View Related
Calculation Of Time Formula
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, H50F50+(H50
View Replies!
View Related
Date Calculation Formula
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
View Replies!
View Related
Formula: Choose The Calculation
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 Replies!
View Related
Step Calculation Formula
I have a simple calculation  Say 06. 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
View Replies!
View Related
Need Assistance With Average Calculation Formula
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(B23AVERAGE(F23:J23))>B20),B23ABS(AVERAGE(F23:J23)B20), IF(AND(K23=F23,ABS(B23AVERAGE(E23,G23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,G23,H23,I23,J23)B20), IF(AND(K23=G23,ABS(B23AVERAGE(E23,F23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,H23,I23,J23)B20), IF(AND(K23=H23,ABS(B23AVERAGE(E23,F23,G23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,I23,J23)B20), IF(AND(K23=I23,ABS(B23AVERAGE(E23,F23,G23,H23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,H23,J23)B20), IF(AND(K23=J23,ABS(B23AVERAGE(E23:I23))>B20),B23ABS(AVERAGE(E23:I23)B20),0))))))))
View Replies!
View Related
Formula/Function For Commission Calculation
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 ag (stuff that is irrelivant) row h, gross fees row i, commission (in dollars)
View Replies!
View Related
No Calculation Flag, And Percent Formula
1. 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.
View Replies!
View Related
Declining Balance Calculation Formula
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.
View Replies!
View Related
Rank Calculation (with Correction) In Array Formula
I'm trying to calculate the sum of rank vlaues in an array formula (required for a MannWhitney Utest calc). For example, I have the results of a survey quesiton (15 rating) with particpant groups of Sales, Marketing & Other. I want to sum the Ranks of the data points that come from Sales or Marketing (but not Other). The added complexity is in the need to add in the Rank correction value to account for ties. The conditional arrays are the tricky part. I'm very close, but the array formula is still including the Other values. If I delete those data points the formula works great. For those with strong stomachs, I've copied my latest formula below. $C$% = "Sales" and $F$5 = "Marketing"... ={SUM(IF('Survey Data'!$D$3:$D$30=$C$5,RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30))+(($M115+$N115)+1RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),0)RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),1))/2))}
View Replies!
View Related
Formula To Count 12 Months Back For Finance Calculation
I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row. We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months. For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets. In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month. Here is the formula: =(FVSCHEDULE(1,M79:M90/100)1)*100
View Replies!
View Related
Formula Structure: The Total Fees (H2) Is A Part Of The Calculation
I am attempting to calculate commission (J2) based on the data entered in cell D2 1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%. 2. If the Loan Description is specifically ‘80/20’ then 80% of the Loan amount is used in calculating the commission. (note: ‘80/20*’ is also a valid entry). Otherwise the total loan amount is used. D E F G H I J Loan Dsc. Int. DSCNT% YSP% Fees Loan Amount Commission 80/20 7.52.0 1.0 3.0 $137,403 $4,122.09 I attempted this formula and obviously it’s incorrect: Calculating Commission (J2) =IF(H2=" "," ",=IF(D2="80/20",(I2*0.80)*(H2*0.01),I2 * (H2*0.01)) 1. If H2 is blank then TRUE enter a blank 2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.
View Replies!
View Related
Formula Not Updating: Using Tools, Options, Calculation, Automatic
I have a table with rows that keep growing. But I have place formula in the whole of column F, i.e. F2:F66565. When I import information from MS Access into column A to E, the formula in F does not work, until I copy from F2 to the end manually. I have tried using Tools, Options, Calculation, Automatic. That doesn't work, I have also tried F9, that doesn't work and I have also tried checking Precision as Displayed under the calculation tax in ToolsOptions, that doesn't work either.
View Replies!
View Related
Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result
I'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.
View Replies!
View Related
Reverse A Dictionary
i have to reverse a very big wordlist containing four coulmns. Column A words, Column B Transcription, Column C Grammar and Column D Meanings. Now i would like to make the meanigs (seperator is ";") to words and words to meaning in another new worksheet added by a macro. For example EnglishFrench would then become FrenchEnglish wordlist. If the original worksheets name is "x" then a new worksheet should be added with name "Rex". This new sheet should then contain the new wordlist. For example:
View Replies!
View Related
Reverse To Concatenate
a way in which to split the following line into say 5 separate pieces George Julius Aaronson (81) 12th Feb 1945 D 9th Jan 1948.jpg George Julius Aaronson (81) 12th Feb 1945 D 9th Jan 1948 so that they are in 5 separate cells on a row. just to make things harder no name in the first colum has the same number of forenames or the same number of digits.there are possibly 600+ of these lines of which the next one is below (Hanna Auerbach (73) 2nd Oct 1949 D 13th Feb 1955.jpg)
View Replies!
View Related
Reverse VLookup
I have a column, call it E. I have table, call it Cities. It has a column of, um, cities, but entered in various ways; and a column of regions: South Cumberland SW So. Cumberland SW So. Cumb. SW Cumberland SW Galveston S Rangeley NE Galv S If any one of the entries in Column 1 is contained in column E, I want my formula to return the appropriate region code for each row of E. Note that E8 may or may not be an exact match for the table entry  it may have additional text before and/or after the match text. It may also not appear in the table at all. I could contruct a whole sequence of "Find" formulae, but I'm thinking there has to be a better way. Also, if I find I need to add another city permutation, it's way easier  & less errorprone  to just extend the table than to create a whole new "Find" calculation column, and then add that column into the calculation that extracts the region.
View Replies!
View Related
Reverse Ranking
I have a column of events,there are 7000 in my sheet.The scores for each contestant in the event are in col.2. In col 4 I have the rank printed for each contestant in each event in ascending order.I want to know how to print them in opposite order i.e highest in each event is ranked 1 etc into column 5.
View Replies!
View Related
Reverse Calculations ..
I dont know if that is the correct title to use but here goes. I am trying to help my friend with some work that he is struggling with. We have a model where we can change the % of the Service Level in field E8 and it will tell us the number of people required within field E17. Is their a way we can reverse this by creating another spreadsheet where we could put in the number of people we have for it to tell us the service level that would reach?
View Replies!
View Related
Another Reverse Concatenation
I need to reverse concatenate a column of addresses, but text to columns won't work. I'd like to have a formula that takes into account each of the following scenarios (basically any standard address you can think of): 102 Bart St 104 Homer Simpson Ave 106 US HWY BSN 805 W 108 N Springfield Rd What I need is to have the result in four columns. The first field would the house number. The second column would be the prefix (direction) of the street name, IF PRESENT (so the first three examples would have no value in the second column, but the fourth one would have an "N". The FOURTH column would have the suffix, whether that is a street type (like Rd or Ave), or a postdirectional like in the third example ("W"). The THIRD column would have everything else (whatever is between the prefix and the suffix). In other words, using the examples above (* indicating a new column): 102**Bart*St 104**Homer Simpson*Ave 106**US HWY BSN 805*W 108*N*Springfield*Rd
View Replies!
View Related
Reverse A List
I've written a list and I want it the other way around. Top at the bottom, bottom at the top. There's no sortable column in it. If it gets sorted it'll be out of order. I just want the same order but reverse the whole thing. I've looked around a lot and failed to find it so far. I've got to do is add a column at the side, number it in order then sort on that column, remove it later, right?
View Replies!
View Related
Sumproduct In Reverse
I'm fairly proficient in Excel, but this one has me stumped (I have attached a spreadsheet to show what I'm talking about): For example, take these two rows of data: 55 , 105 , 101 4 , 3 , 1 The first row represents the production level in years 1, 2, and 3 of production of the life of a unit. The second row represents the number of units added in a particular year. So, if you are in the third year, the one unit added that year will produce 55 widgets, the three units added the previous year will each produce 105 widgets, and the four units added two years ago will each produce 101 widgets. So: 1*55 + 3*105 + 4*101. The result for this formula would go underneath the "1" (ie its the total production in year 3, from all those units added in years 13). Then when we roll through to the fourth year, where 2 units are added (and where we have the extra info that in the fourth year of production, a unit produces 50 widgets) it looks like this: 55 , 105 , 101, 50 4 , 3 , 1, 2 2*55 + 1*105 + 3*101 + 4*50. The result for this formula would go underneath the "2" (ie its the total production in year 4, from all those units added in years 14). I need to produce a single formula which I can drag across the row. Without having a single formula, I need to copy the formula across to the cell to the right (with "$" in the right places), then add a new element to it. Seems very inefficient (take a look at my attachment)
View Replies!
View Related
Reverse Twoway Lookup
I know how to do twoway lookup with the INDEX and MATCH functions (example, =INDEX(B2:H11,MATCH(A6,A2:A11,0),MATCH(E1,B1:H1,0)) to lookup a value in a table given a value in the row range and a value in the column range. I also know how to use the space intersector to do a twoway lookup (example, =A6:H6 E1:E11). My question is, is it possible to do the reverse. Instead of finding the row and column positional and then returning the value at the intersection of the two, is it possible to have the value at the intersection and then find the two values in the row and column positions? So this would mean somehow returning two values in two cells.
View Replies!
View Related
Reverse Match
I am trying to find a way to use the match function to return the last matching value in an unsorted row. For example if I have (with each individual number representing a new column): A: 4 5 4 3 6 7 5 4 5 0 2 6 3 B: 5 I would like to get a formula similar to this one =match(B1,A1:13,FALSE) to return 9 instead of 2 like it does now.
View Replies!
View Related
Reverse Two Way Lookup With Duplicates
I have an appointment schedule shaped like a table and I need to extract data from it into a sub table. The schedule has date column headers, time row headers, and the intersection of the date and time headers is a cell with a patient’s name. There are duplicate patient names in the schedule. The extract table should list the patient names vertically and next to each patient name should be a list of dates and times. The time and date for a single appointment should be contained in one cell. For simplicity, I will not use dates and times in my example, but instead I will use letters. Schedule looks like this: Date Column headers are as follows: B1 = m, C1 = n, D1 = o, E1 = p Time Row headers are as follows: A2 = q, A3 = r, A4 = s, A5 = t, A6 = u, A7 = v The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin The extract table looks like this: Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3 Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux The cells that contain times and dates are: C21 = “r, p”, C22 = “q, m”, C23 = “r, n”, D23 = “s, n”, E23 = “q, o”, where the first letter is a time and the second letter is a date. A while back I posted a question about this sort of reverse two way lookup: [url] Since then I have encountered the problem of the patients showing up in more than one time slot on a single day and also showing up in the schedule on more than one day. The formulas I am using are not working. So in my example, the trouble I am having comes from the fact that Sioux’s name shows up in more than one time slot for one day and also he shows up on more than one day. If it is easier to see the table with dates and times, the data is here: Schedule looks like this: Date Column headers are as follows: B1 = 1/8/2010, C1 = 1/9/2010, D1 = 1/10/2010, E1 = 1/11/2010 Time Row headers are as follows: A2 = 8:00 AM, A3 = 9:00 AM, A4 = 10:00 AM, A5 = 11:00 AM, A6 = 12:00 PM, A7 = 1:00 PM The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin The extract table looks like this: Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3 Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux The cells that contain patient names are: C21 = 9:00 AM, 1/11/10, C22 = 8:00 AM, 1/8/10, C23 = 9:00 AM, 1/9/10, D23 = 10:00 AM, 1/9/10, E23 = 8:00 AM, 1/10/10
View Replies!
View Related
Reverse Index/Match
I've got a chess board in B2:I9, and would like a function that returns the location of a particular piece. Say I have Q1 in cell B9, I'd like to return A1 B1:I1 = A > H A2:A9 = 8 > 1
View Replies!
View Related
Reverse Engineering Function
I am half way through my VBA for beginners book and did one Excel VBA tutorial lesson online. I am trying to learn by reverse engineering the 2 written functioning programs I have now. Today am posting the 2nd program
View Replies!
View Related
Reverse MID  Search From Right
I need a formula that would search a string in a cell for spaces starting from the right. similar to MID but with the start and end pos reversed. eg. col A below stores the text. col B to D is where i need to write the formulaes and drop down for the number of rows in A (in this case 3) A 33 444 555 444 55 666 666 777 98 88 here is what the result in cols B C and D should be after the formula and drop down: B 555 666 88..................
View Replies!
View Related
Reverse Data In Columns
I have a list of search words in column A and in column B is the products that address that particular search word and the products are separated by a comma in column B. Each product is listed multiple times under various search words. What I am hoping to do is reverse this so that the product is listed in column A and the related search words are listed under that product in column B.
View Replies!
View Related
Reverse Order Of List
I have a question on paste special. Is there a way to paste special reverse the order of numbers? I have data going in reverse chronilogical order vertically on my spreadsheet. So for example, 3/1/07 12.3 ( Cell A1) 2/1/07 13.2 (Cell A2) 1/1/07 14.5 (Cell A3) 12/1/06 16.7 (Cell A4) I want to now paste this data vertically (so transpose which there is a handy check off box for) BUT ALSO to Reverse the data so vertically it now reads : 16.7 (in cell A1 for ex) 14.5 (In cell B1 for ex) 13.2 (In cell C1) 12.3 (In cell D1) Is there a way to paste special reverse the order of numbers?
View Replies!
View Related
Export Access, Or Reverse.
Excel has a DATA  IMPORT EXTERNAL DATA  NEW DATABASE QUERY Function built into the toolbar. I need to lookup a table in access **TableTest**. Find the Record with a **Door_Number** Equal to an input I change everytime. Then I need to lookup a table in access **TableTest2**. Find a Record with a **Door_Number** Equal to an input I change everytime And then paste the records in a line going DOWNWARDS not Right to Left. To summarise. Hit button, Type Key1/2 input, find record(s) paste into excel. Im afraid providing a sample is gonna be a little hard on this one, my files are HUGE
View Replies!
View Related
Reverse Before Double Click
I am trying to create code so that when a cell in Column C (for example C1) is double clicked, it makes the font bold, italicized, blue and inserts a row below the double clicked cell. I am also trying to make it so that if the cell is double clicked again, it removes the bold, italics, blue and deletes the row that was previously inserted. Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Excel.Range, Cancel As Boolean) If Target.Font.Bold Then ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Select Cancel = True If Not Target.Font.Bold Then ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Delete Shift:=xlUp ActiveCell.Select Cancel = True End If End If End Sub
View Replies!
View Related
Reverse Mortgage Calculations
I set up a formula knowing only two variables? 1. Assuming I know how much per month one can pay in total, and 2. The percent interest rate ... I need the total amount that can be financed. Example: A. $3,900 maximum per month B. 5.00% interest rate C. = x (the amount to be financed)
View Replies!
View Related
Format: Number And Reverse
I have a userform into which a user can enter a number. When they exit the TextBox the number that they entered must be formatted into the number format. eg 12345,67 > 12.345,67 and 12345,67 > (12.345,67). To do this is not the problem: formatnumber does the trick. What I am struggling with is to get VB to still recognise this as a number. I need the code that does the opposite of formatnumber. (12.345,67) > 12345,67. I excell it works perfectly when you format a cell as custom: _(* #,##0.00_);_(* (#,##0.00);_(* ""??_);_(@_). I want to be able to do the exact same thing in VB.
View Replies!
View Related
