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
=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.
If 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 want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet
i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.
Numbers with Dr as suffix denote positive numbers
and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.
I know ASAP has a feature to do this but I need the code in a bigger macro that I wrote.
EX: -1 needs to be 1
I have been looking through the forums and found the below code, but I have both text and numbers in the same range. I have attached an example of what I need to have converted.
Book1.xlsx
I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.
View 7 Replies View RelatedI 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 RelatedHere 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,...
Assuming 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.
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.
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 RelatedI 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 Related