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 have a list of YES or NO answers in a sheet. I want to calculate the % of YES in the list. E.G. if there are 100 cells in total and 25 of them say YES and the remaining 75 say NO, the percentage should be 25%.
I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.
A B C 7/1/2012 0:00825-CL-A-41091-REG4.00 7/1/2012 0:00825-CL-A-41091-REG6.25 7/1/2013 0:00825-CL-A-41456-REG4.25 7/1/2013 0:00825-CL-A-41456-REG6.50 1/1/2014 0:00825-CL-A-41640-REG4.25 1/1/2014 0:00825-CL-A-41640-REG6.50 3/1/2014 0:00825-CL-E-41699-REG3.00 3/1/2014 0:00825-CL-E-41699-REG4.00
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
I have 2 columns (A and B) of text, A and B that needs to be turned into numbers in column C. The text in column A can also occur in column B and vice verse. There can also be 2 or 3 values in column B, there is no "," to separate the values only spaces. Every text name has a value (they are explained in the file but not included in original files). If there is text in both columns it has to add the value for each text. I need to get column C calculated by using VBA. Column C is empty from the start and the rows can be +500.
In cell A5 (please see the excel attached) I have a weighted formula that is supoosed to return a final result based on the drop down from cells B5 to V5. Drop downs give the option of choosing a value. Everything is going fine as long as you select a value (5, 3 or 0).
In cells H5, J5, K5, R5, S5, U5 there is also the option of choosing NA. I am trying to get the formula to calculate NA as being equal to 5. Basically, if for the respective parameter someone choses NA, the final result in cell A5 should not be influenced.
If all the parameters are 5 and only one has NA (any of the cells H5, J5, K5, R5, S5, U5), the final result (in cell A5) should be 100.
The same applies if all parameters are 5 & more than one cell or all the cells that have the NA options (H5, J5, K5, R5, S5, U5) are NA.
On the attached spreadh sheet I have production costs that I wish to calculate. There are certain courses that are a constant each month and they are input via a data validation list, however there will be courses that are run that are not on this list.
on the cell with 'Other' I want to total the costs for the courses that exclude the normal monthly courses.
Ive managed to use the formula below to exclude certain text but I cannot do it for multiple keywords, or maybe there is a simpler formula. =SUMPRODUCT(--(G4:G31>0),L4:L31,--ISNUMBER(FIND("Suicide",G4:G31)))
I have thousands of lines in a txt file and would like to total the sum of a field. here are the few lines in the txt file.
I would like to calculate the LPG_Amt for each of the Company_code and the count of number of lines for each of the Company_code. Request the members to help me with a vba code that could run in an excel file and give the output in excel.
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.
I'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 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.
I am compiling data from several different, variable, worksheets. I am using concatenate to add the name of each worksheet into a formula to pull the appropriate data.
However, it is returning the text of the formula instead of the results of the formula. How can I make it calculate the result instead of simply displaying the formula text?
Is there a way in Excel/VBA to calculate the width of a text string (in points or inches) with a specific font/font size/font attributes? Right now I have to use a static character count which assumes worst- case character widths for the font (Arial 8 point) but leaves unused whitespace to the right when the text string uses narrow characters.
I 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`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
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.
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????
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?
Something 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,
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.
