I have few x values
I have few y values corresponding to the x values.
How to calculate a x value from given y value based on the above data. I tried linear iterpolation, I am not getting good results.
I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:
Cab Type in cell () Indica
If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800
For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2)
Cab Type in cell () Indego
If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200
For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)
Ther are about 5 different types of cabs in the sheet.
Cab Type Provided
Total KM run
Basic Amount
Extra km
What I want to achieve is as under:
1. If value of cell A1 = Indica and value of cell B2 is entered as 40
Then the values of cells (C2:e2) should populate automatically as 400, 0, 400
2. If value of cell A3 = Indica and value of cell B3 is entered as 40
Then the values of cells (C3:e3) should populate automatically as 400, 40, 440
How to calculate the monthly sales based on the data given ?
View 4 Replies View RelatedI have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns
(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns
It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.
So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.
I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.
Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.
The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.
I'm trying to design a Profit and Loss template that calculates off an accounting software data dump. I will attempt to explain the problem here but I have included a summary in the linked workbook which would be easier.
DOWNLOAD WORKBOOK: [URL]
I have two problems:
The Formula: Basically, I need to calculate every cell in the Profit and Loss statement based on the below criteria: The Profit and Loss item accounts, e.g. Gross Income has 6 accounts. There is a reference table for these - so incorporate index match/vlookup? User selected data from 4 combo boxes.
I am thinking a SUMIFS formula (using the combo box values) and somehow combining a vlookup to pull the items accounts. I'm starting to think that VBA is the best method?
Tweaking: The attached workbook is a small example. The full data dump contains 60,000 rows.
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
View 3 Replies View RelatedI have series of data values like below. I have to find Maximum, Minimum values for each of these values.
9430
9822
10070
[Code].....
I'm selling a product to clients. The first one is $20, and every subsequent one is $10. If they don't order any, I need the cell to read $0. I've been trying to get the IF function to work, and can't quite make it work correctly.
View 3 Replies View RelatedI've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.
At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.
EXAMPLE:
TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00
Employees - 4
SUM - £4,237.86
Median - £1,033.93............................
I want to apply 4 conditions.
Core ValueCurrent Pricenew Price
a 400
b 600
c 300
d 300
If core value = a then new price should be current *2%, if core value=b, then new price should be current price times 2.2%, if core value = c than new price should be current price times 2.5% and if core value=d, than new price should be current price times 3%?
also would i be able to do a formula if core value is either a or b, than new price should be current price times 2% and if core value is = to c or d, than new price should be current price *3%?
I am working on a tax deduction formula for payroll. I need 3 formulas. The first formula needs to find two cells that are on the same row based off the employees status and gross income.
View 5 Replies View RelatedI have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.
Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.
Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.
I have 2 tables:
IDTypeValue
1A3
2A3
3A4
4B4
5B3
6C2
7A4
8A3
And
TypeTotal
A
B
C
I need to "total" column to automatically display the sum of the values for the categories A, B and C. Is there a formula I can use for this?
I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns
0 - 99,999 0.05
100,000 - 499,999 0.04
500,000 - 999,999 0.03
1,000,000+ 0.03
On a separate worksheet I want to add I6+I7, depending on the value multiply it by the value in the second column. So if the total is 520,000 then multiply it by 0.03.
Need to calculate different percentiles based on different grades. how do I put an 'if' loop or anything else that will work for this.
GradeAmount
B23
B22
B35
B36
B48
B49
B510
B511
B615
B23
B37
B614
Need function for the table below
Percentile10%25%50%60%75%90%
B2
B3
B4
B5
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:
KEYPerformance
2010,000
2120,000
2230,000
2340,000
I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:
KEYMin FeeFee 1Fee 2Fee 3Fee 4
201000.10.20.30.4
211000.10.20.30.4
221000.10.20.30.4
231000.10.20.30.4
For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:
Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999
So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.
How can this be combined into a lookup/range/low-high formula to extract the correct values????
example 1:
This years sales are $3700, a decrease of 11.6%. What would last years
sales be?
example 2:
This years sales are $4500, an increase of 151%. What would last years
sales be?
I want to be able to calculate a price based on a given quantity. But here is the trick.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 36 widgets, then the cost is 36 x $1.00.
If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 4 widgets then the price is 4 x $1.15
But, if the customer orders 35. Then the first 24 are calculated at 24 x $1.00 and the remaining 11 are calculated at 11 x $1.15.
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out.
Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
I have some financial data that has been exported via sql from out financial system that lists transactions for each month in a column with 12 columns representing each month of the year in Sheet1. (My financial year runs July - June). The column headings are MONTHLY_TYR_BALANCE_01 thru to MONTHLY_TYR_BALANCE_12 where MONTHLY_TYR_BALANCE_01 = July, MONTHLY_TYR_BALANCE_02 = August etc.
To the right of this data I wanted to have one column that will list the MTD data each month ie in July this should reference to the column headed MONTHLY_TYR_BALANCE_01, and in August this should reference to the column headed MONTHLY_TYR_BALANCE_02
I can get the end user to type the month number into a field and reference back to this but I just can't think how to convert this to a column reference that I can then use.
I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.
Excel File as follows:
Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)
I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.
I have a number which consist of 12 digits such as 765432123345. The first two digits denotes the year. I need to create a formula in the next column to calculate the difference in years by comparing to the current year of 2013. In this scenario, the difference will be 37. Is there a way to do this?
View 5 Replies View RelatedSomething has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase. My questions have two parts:
1. Is this something array formula can handle in one formula?
2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps. For example,
Month 1-15 Rent $20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30......................
I am trying to work out FX P&L by linking trades recorded on a spreadsheet to FX rates depending on which way the currency is quoted. I have attached my spreadsheet to the post. In the Worksheet 'Daily' In Column L, I want to calculate the USD Equivalent for all the trades recorded, by perfoming a simple calculation linked to the FX Rates in the 'Summary' Worksheet.
If on a trade, the Accounting Currency (P&L Currency) Is USD, then I just want the cell to equal the amount in column J. If however the P&L Currency is any other currency, I want it to divide the amount in that field to the corresponding rate in the 'Summary' Worksheet. For example, in Row 108, on the Daily Worksheet, the P&L currency is JPY. I want to divide the JPY amount in J108 by the JPY fx rate in the 'Summary' Worksheet and return that number in L108, so the answer would be $59,353.47.
I am working on my companies tax reports for the first time. The wages for unemployement and FUTA do not calculate IF the total wages are over a certain amount.
Im trying to get the formula to see if the total wages are over $7000 then dont calculate. Ive tried several different things from the links, but still can't make it work.
total wages are $7800, so i only need to calculate it on $7000 and then not calculate it again for the next quarter.
I have events in column 1,in my sheet there are about 800 events.
In col 2. each contestant has a label,blue(b),green(g) or red(r)
In column 3 I want to print the % of blue + green of the total for each event,
e.g first event in attached sheet would show 77.7% down to row 10 in col 3 etc.
I am trying to create on excel order form. I want customers to be able to input the item # (a range from 1 to 12), then I want the to price to be calculated based on the item # they input.
For example. If they choose item #2 in A6 then the price in F6 will be recorded as $8.00. (the price would change for each item # they input).
the formula I started out with was:
=IF((A6=1),"$8.00")
this worked for me if A6 did in fact equal 1.
So I tried adding this equation to the formula.
=IF((A6=1),"$8.00")*OR((A6=2),"$7.00")....this would continue on. I even pressed "command return" after the statement as if I was entering an array formula.
I got the error #VALUE!
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.
The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.
I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.