Here is my formula.
=If(Sheet1!J5=$J$4,0,(Sheet1!$J$4-Sheet1!J5)*Sheet1!$J$1)
It is perfect, except it doesn't have one final step. What I need it to do
is be able to do that ONLY if it is greater than or less than by a specified
amount. So there needs to be a modification of ... Sheet!J5<> (but by 100 or
any other number that I set) $J$4,...
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount
5-10 items they receive 2% discount
11-20 items they receive 5% discount
21-50 items they receive 8% discount
over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
I have part if the code here’s
PHP
Range("f" & indexI + 1).Value = Format(Strl, "00:00")
to return value = time formats
How do I modify it to + 0.1 minus?
Sample
IF the return value = 12:00:00
Now I wanted = 12:00:01
Trying to work out the formulas for placing plus minus variables above and below a cell as per worksheet attached. right hand side of the page
View 2 Replies View RelatedHow can i produce "tan to -1 " in excel ?
View 9 Replies View RelatedI use a formula that has a date in it 11/15/13 for example. Then I have a formula that calculates "days" (15), but I want to subtract one. How do I do that?
View 2 Replies View RelatedAssuming the first date is in A1, and the second date is in B1, in standard dd/mm/yyyy form, my current formula is =B1-A1-1.The '-1' is due to the fact that if a patient stays for 10 days, they will only spend 9 nights in the hospital. (Bed Nights).The problem is, the formula is stretched in the total column from, say C1 to C50. Each one of these has, or will have, a number of days in it.However, due to having th '-1' in the formula, empty rows that are yet to have a patients details inputted have a -1 where I need a 0. The only reason I need to change this is because I need a running total of the bed nights of all the patients.I think the formula I'm after is something along the lines of; 'If cell B2 is empty, input 0. If B2 has a date, use formula 'B2-A2'
View 9 Replies View RelatedI want to save phone no as +99 9876543210 in excel 2003 on my xp pro machine. But if i give a + sign in the cell, some blue dotted rectangle shows up and everything messes up.. I think it is treating it as a formula or something... how can i save this in the cell. tell me in detail if you are going to tell me about macros or vb code as I don't know how to insert code or program macros.
View 3 Replies View RelatedWith out using Conditional Formatting, how can I check a cells value if it can fall within a range of +-.002?
I have this code but it seems to have limitations.
=AVERAGE(INDEX(E5:IV5,LARGE(IF(E5:IV5<>"",COLUMN(E5:IV5)-COLUMN(E5)+1),5)):INDEX(E5:IV5,MATCH(9.99999999999999E+307,E5:IV5)))
I have this formula where it averages the last five numbers in a collumn, but I want to average the last 5 numbers less the max and min number.
I need a formula that that will show £0.00 if the amount is under zero, i have a formula k15/o15 that gives me a amount but if this is under zero i need this to show as zero not -£70 for example.
View 3 Replies View RelatedI am using time based on the 1904 calendar, this is so time can be shown as a minus figure when to cells are taken away and the result is say -17:25. What I want to do via conditional formatting is show any minus hours as red. Tried various options but I have had no luck so far. or can this be done via VBA?
View 6 Replies View RelatedI have a table in a sheet where the rows of data are filled in automatically from other sheets. Each row represents a different week. The data, (lifted from from the relevant worksheet for each row) represents the no. of calls in each week.
Now, I've set up an average to be calculated from the data - it'll do this for every row as long as there is data present. (If the row's corresponding worksheet doesn't have any data the result will be "NA()" - not included in the calculation.)
However, I don't want the average to include the no. in the "most recent" row, as this is "live" data and until the week is over does not represent the data for a full week and therefore skews the average.
So I want the average to exclude the data in the most recent row with a numerical value in it and not a "NA()"
ie - column could be ......
I need to write a formula to minus a percentage of a total.
for example I want to take away 20% of the total of £8.75.
My file name constantly changes, it will always be (minus 2 business dates from today)
Now when my macros run it goes between different worksheets so I need to tell it to go to right workbook. I tried below but getting an error 438 'Object doesnt support this property or method'
Code:
Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xls").Activate
Sheets("data").Select
I need to sort some data on a spreadsheet but I am uncertain on how to select the used range.
I just want a piece of code that will select WHATEVER area of the sheet is populated with data. The sheet I will be doing the selecting on is titled "Master".
To add to it I DO NOT want ROW 1 included in the selection.
In an excel file i have two fields,
Month to Start - is a combobox with 12 months, next is
Month to go back - is a textbox, here specifying number of months to go back.I need to store month names by minus the existing month - number in a textbox
eg: if Month to Start is 'September' and Month to go back is 2, then what i need is September & August in an array or separate variables.
How I can minus number from a month?
I have a spreadsheet which contains values and text. Would I be able to change selected values to a minus value by clicking one button?
A2 = 200
A3 = 300
A4 = 1000
B3 = 50
C5 = 600
Changes to
A2 = -200
A3 = -300
A4 = -1000
B3 = -50
C5 = -600
I would also like the option to change them back again as well.
I have about 40 values so do not want to do this manually.
How do i stop changeCell from going into the minus?
Sub goalSeekSample() Dim seekValue As Double Dim changeCell As Range Inflation = Range("O7").Value seekValue = InputBox("What's the value to seek?") For Each changeCell In Range("N18:N32").Cells changeCell.GoalSeek Goal:=seekValue, _ ChangingCell:=changeCell.Offset(0, -4) seekValue = seekValue + (seekValue * Inflation) Next changeCellEnd Sub
I have attached here an excel sheet with some data. I need to show the minus value in D5 as a plus sign, is there any conditional formula to work this out??
View 4 Replies View RelatedI am trying to write a formula which takes todays date and goes back 1 month.
Eg Today = 20/11/2008, however my formula would give me 20/10/2008
I attached an excel file for an example. Basically I am calculating on time shipping, but I want don't want Saturday and Sunday to coun't against us.I need G5 to equal 3 days. Right now when I subtract E2 from F2 I get 5 days. But we only work M-F, and Saturday and Sunday shouldn't count against us. How would I subract the days automattically, so it takes out Saturday and Sunday?
View 10 Replies View RelatedIf I wanted to get a cell, say A1, to multiply a value, for example 7, by the results in another cell, say B1, but NOT multiply if that cell's value is a minus number, is there a way of getting Excel to do that?
Normally I'd just type =SUM(A1*B1)*7, but how can that be reconfigured to ignore minus numbers? e.g. if B1 contained -5, ?
I have a time calculated and I want to know, Out of that time how much is above or below 8 hours.
Ex. 1: 7:30 - 8 = [result] "-:30" mins
Ex. 2: 11:00 - 8 = [result] "3:00" hours
OR
How can I convert 7:30 into 7.5 [decimal] and then subtract that from "8" to get the difference?
I know this is an easy one, I have done it many times before. It's crunch time and I am drawing a blank.
I have been creating a new spreadsheet in work to show materials variances
I am having a problem with a minus figure in a calculation as follows
expected opening stock = (172)
Actual opening stock = 1022
My return cell has the following formula (Actual Opening Stk - Expected opening stk)
I should get the answer of 850 - I am actually getting 1194
I would like to put a format on all cells that are below -15:00 (that's the
cell format).
So, e.g. -20:00, -50:00 but NOT -10:00.
Do I use greater or less than? I cant seem to get any to work!
I have already put a format on the cells for greater that 15:00 which works as it's whole numbers - although it has changed the 15:00 in the conditional formatting cell to 0.625 but still seems to work?
I have a static date and military time in B6 (5/10/07 18:00) I have to write various formulas to reflect 1 month (whatever calendar month it ends up in, so not necessarily 30 days) minus 7 days and then the same formula plus 7 days. How is this written?
View 9 Replies View RelatedI use the first six numbers of a SA Identity number to calculate the age of a person as these six digits are actually the year (yy) followed by the month (mm) followed by the day (dd), I am born 01 September 1962, and therefore my first six digits are 620901.
Assume the figure 620901 is placed in Cell A1
Now, in another cell, say A4, I have a future date, but this value is formatted as dd/mm/yyyy. Assume this value to be 01/10/2017.
Firstly, I'd like to know How old I AM at that date and secondly, just because I am battling so, how old I will be on my next birthday, because adding the figure 1 to a total has never been so useless - it just doesn't work! I tried adding all sorts of numbers for months and days in a year but there was no consistency.
So here is my question: Simple - how do I get this to work?
On 1/10/2017 I will be 55, or turning 56 At Next Birthday. I have the following function that gives the answer of 55, but not 56 ANB even after 1 additional year is added to the function (the cell is formatted as yy):
Cell A6 Function = A4-DATE(LEFT(A2,2)+1900,MID(A2,3,2),MID(A2,5,2))
Also, ON my birthdate, 01/09/2017, it says that I am 54, and it has to be wrong because I would have turned 55, unless the function uses time and not just the actual date somewhere. On the next day it does, at least, see me as 55.
how to add calculate what my age will be AT MY NEXT BIRTHDATE for any given date in A4?
Sub TrimSlashesToRight()
Dim cell As Range
For Each cell In Range("AN2:AN2000")
cell = Left(cell, InStr(cell, "//") -1)
Next
End Sub
I cannot get it to run this, it always crashes on
cell = Left(cell, InStr(cell, "//") -1)
It will work fine like:
cell = Left(cell, InStr(cell, "//") + 0)
but then it leaves the first / in place, i want it to start deleteing before both //.
I have many regularly used workbooks with financial data in them. Many times the financial data needs to be adjusted due to rounding issues. To do this I add a +1 or -1 or sometimes it is in decimal form +/- .1 Also the number "1" is not always (but almost always) used. There are some circumstances where I may add + 2 or +6 or any small number to the end of a formula.
My problem is that when I use the file again after making these changes, I am wanting to remove the "adjustments" I have made to the formulas. Sometimes a green triangle will appear in my cell telling me the formula does not match that of other near-by cells, and that is good, but it is not consistent enough to find them all, or even most of them.
What I am wanting is a macro I can run that will identify any cell with these "adjustments" I have added to them and I will then go in and delete the +/- 1 or whatever the number is. I can easily do a Find "+1" for the entire workbook, it is the range of possible numbers that is preventing me from doing a simple ctrl + f.
