Is there a formula or combination of formulas that will return the word "Order" if the values in a column go above a certain threshold then start to decrease?
For example we have a list of data in a column that is imported each hour from an external database. Each new value is copied into the next cell down in column B. If the latest value was to go above, say, 100 is there a way of returning "Order" as soon as one of the subsequent values is lower than the previous one?
In the attached table the fees generated are on a sliding scale and the total is the fee generated within these ranges
I would like a formula in b10 to give me the value based on the total sale figure, i.e. 80.00. I've searched the board and tried some very long "if" statements and "lookup" table but to no avail.
Is there a formula I could use to calculate the desired value, preferable using cell references and not the actual values.
I am trying to find a way to identify the nmber of periods where a particular value is exceeded.
Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days.
Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods.
I have a financial model that returns ratios for various years. I would like to highlight in the summary part of the model those years where the ratio is over a set threshold.
For example:
A B C D ...
1 2013 2014 2015 2016 ...
2 31% 29% 41% 28% ...
3 Max: 30%
4 2 years over Max: 2013, 2015
In the above example, the threshold (Max) is 30% (cell B3), so the value in A4 would be "2 years over Max: 2013, 2015"
I have been able to do it manually by putting together a COUNTIF function along with various IF statements as follows: =COUNTIF(A2:D2,">="&B3)&" years over Max: "&IF(A$2>$B$3,A$1&", ,"")&IF(B$2>$B$3,B$1&", ","")&IF(C$2>$B$3,C$1&", ","")&IF(D$2>$B$3,D$1&", ","")...
I even managed to get rid of the final "," by adding a second "," at the end and replacing the expression ", ," using the SUBSTITUTE function (yes, I am a bit **** when it comes to details).
My problem is that I currently have 16 years of projections and, although the above formula works, it requires manual changes every time I add / remove years.
I know that I can do it easily in VBA but the Excel file is to be shared with others via email and I know that their systems are setup to deactivate macros by default (and I don't want to rely on the user having to manually activate macros).
1 TO 10 25.00 11 TO 50 15.00 51 TO 100 9.00 101 TO 250 5.40
In this there is an area where it is cheaper to buy for example 12 instead of 10 and I am trying to work out a formula to deal with this funny step change down as people buy more.
The user enters data into Column E on Sheet1 and i want my code to display a pop-up box when a cell's value exceeds 500. I've tried the two codes below which i thought would work as Excel didn't highlight any breaks when i wrote the code, but no pop-up box is being generated when values > 500.
ATTEMPT 1:
Private Sub Threshold_Check2(ByVal Target As range) Dim cell As range
For Each cell In ActiveSheet.UsedRange.Columns(5).Cells If cell.Value > 500 Then MsgBox "Value within 15% of Threshold" Next cell End Sub
ATTEMPT 2:
Sub Threshold_Check(ByVal Target As range) Set Target = range("E1:E150") For Each cell In range("E1:E150") If Target.Value > 500# Then MsgBox "Value within 15% of Threshold" End If End Sub
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
is there a way to create a formula that caps at a certain amount?
i am trying to create a spreadsheet for a group of doctors and their insurance coverage. i want to display that a certain amount of coverage caps at a certain price over the course of a number of years.
so basically, can i create a formula that automatically adds the data and max's out at a certain number?
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
Need count formula to count records with amounts in either columns E, F, or G. For example
Need a formula (not VBA) in cell F2 to return a count of 5 records counted that have an amount in column E, F, or G (but only count as one record when amounts exists in multiple columns):
I'm trying to enter the summation formula into three columns that vary in placement based on the value of num_objs. Basically, the number of grey columns in each of the three sets will equal num_objs. I want the user to be able to enter values into the grey columns in the middle section and the values of the two rightmost white columns (with '0's in them currently) will change accordingly.
My current code is below. However, I keep getting a compile error saying "Expected: end of statement". I've been searching for a solution online for a couple days now, and I don't see how my code is different than what I've been seeing of other people using variables in their formulas.
I have a row A through N. In cell A1, have a dollar amount. In cells C1 through N1, I have different dollar amounts. In cell O1, I want to have a formula that will take the dollar figure in A1 and subtract from that amount the total of all the amounts in cells C1 thorugh N1. But I don't know how to write that formula.
Let's say I've got $100 in A1, and then in C1 through N1, I have $2, $3, $4, etc. for a total amount of $94 in cells C1 through N1. I'd like to have a formula in O1 that would show $6.
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet.
I can get a grand total by summing the unique items by using this formula:
and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take."
However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO $ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have $4,200 listed in each row for invoices 1 & 2.....
(To keep things simple from left to right Column A-H)
The Amount Column seems to be my problem, it has the formula =H98*B98 just a simple multiplication formula to get my unit price x my qty. When I delete the formula in the amount the unite price #value! error goes away. and all that is in the other error box is =IF(P98>0," per piece","") it just puts "per piece" in the box when something is typed.
I have a vlookup formula in Column F (thank you VoG) =IF(ISNA(VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)),"",VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE))
My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
The situation is that I have 3 interest income from 3 different companies. Column B acts as a helper column to determine if a partner receives just one interest income or receives multiple interest income. If they receive multiple interest income, I want column B to display a 1 and if not make it blank. If you notice partners 5, 9, and 16 contain two or more interest incomes (Columns C - E). All the cells are have either an amount (positive or negative) or a zero. There are no blank cells. Columns (F - H) contains the interest income amount if only that partner receives just one interest income. So for those partners they should not display any of their amounts. The reason is because I am using a word template and currently have to use multiple templates ( 3 in total) to display correctly for each partner. I got this to work with a word macro that will collapse all non blank columns in a particular table. This might be on a ongoing project for me as I do have more logic to solve.
I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.
I added a new cell called Track Back on the employee search sheet.
What I want to do is only show variances for the amount of days back selected in the Track Back cell.
For example if I select the last 30 days, only the last 30 days would show up below in the sheet.
I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
I need to calculate the subscription amount based on the capacity of the wind mill.
The slab rates are as follows: 1. Up to 0.6 MW - Rs.3,000 2. more than 0.6 up to 3MW-Rs. 7,000 3. 3 MW up to 98MW -Rs. 7,000 for the first three MW and Rs.1,500 per MW in excess of 3 MW 4. Above 98 MW -Rs. 1,50,000
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
