How To Create Formula For Stop-loss Logic

May 21, 2014

I'm trying to create an excel spreadsheet that keeps track of the highest value from a dynamic series. First a bit of background:

A manufacturer produces widgets made from gold. Therefore in order to quote manufacturing prices, they typically get a live gold price using Excel's web services and if the live price is higher that the last gold price they used, then they update their base price list for gold components. If the live price is lower than the last gold price they used, the last gold price does not change.

I'm trying to produce the above logic in Excel using:

Cell A1: [Excel data connection to pull live Gold price] - no problem here

Cell A2: dynamic 'price' that only updates if cell A1 is higher than cell A2

Cell A2 is where I'm getting stuck. I can't think of how to create a formula that updates cell A2 if cell A1 is higher.

View 3 Replies


ADVERTISEMENT

Unable To Create Simple Win / Loss Chart

Dec 4, 2012

I have a # of games played column (Column A), a # of games won column (Column B), and a number of games lost column (Column C).

I'm trying to make it so Column C automatically enters the number when I put in the number in Column B.

Example:

Games Played........Win........Loss
1.........................1...........0
2.........................1...........1
3.........................1...........2
4.........................2...........2

For the fifth game played, if I won it, how do i make the 2 to automatically enter into the loss column when I put in the number 3 in the win column?

View 2 Replies View Related

Formula For Calculating Win/loss Streaks....

Jul 6, 2004

I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?

View 9 Replies View Related

Logic Formula To Calculate Distance

Jul 7, 2009

I have a excel sheet with city names in Column A, Distances in Column B, and Distances in Column C.

I wanted to write a formula so excel checks the distance between column C and column B and if there is less than a 10% difference between the two then it would copy the name of that city into another column D.

View 4 Replies View Related

Complex Logic Formula To Delete Values

Nov 14, 2009

upon further consideration I thought that it would be best to probably do verything in two passes or maybe not see below macro to get an idea of how the end process should function

View 4 Replies View Related

Excel 2007 :: Stop Links Changing When Use SaveAs To Create Backup?

Sep 25, 2013

I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database

When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.

Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?

View 2 Replies View Related

IF Formula: Conditions Within The "logic Test"

Nov 13, 2008

I'm trying to use the following IF formula:

=IF(J2>$J$143 and J2<$J$144,"1","0")

But it doesn't work - I obviously have the formula wrong. The problem arises with the inequality. how to have two conditions within the "logic test".

View 4 Replies View Related

IF Function Formula Logic: For Every Increase Of Of Mhz, Give 25.00 Per 100 Increase

Nov 2, 2006

I have a test due in the morning, and I really need this question answered ASAP, if anyone could. I need to create an IF formula for this situation: the standard Mhz is 500. Give 55.00 for that standard. But for every increase of of Mhz, give 25.00 per 100 increase.

View 2 Replies View Related

Complex If Statement To Calculate Date Range From Create Account To Stop Account

Sep 7, 2012

Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.

original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}

New formulas needed or revised from the above:

Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)

Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"

Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.

Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)

[Code] .........

View 1 Replies View Related

Stop Zero Value In If Formula

May 11, 2009

I have the following formula: ....

View 9 Replies View Related

Stop Macro: Button To Hit Or Better Just Some Keys To Hit To Stop It Without Using The Ctrl+alt+del Which Closes Everything

Dec 13, 2006

my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.

View 4 Replies View Related

Stop A Date Countdown When Already Have A Formula

Aug 14, 2014

I am trying to stop a countdown of days when a value is entered into an adjacent cell. I have the following formula in E4:

=IF(NOW()<D4,DATEDIF(NOW(),D4,"D"),-DATEDIF(D4,NOW(),"D"))

I want this countdown to stop when I enter a date into F4 and that the number of days still shows within E4.

View 1 Replies View Related

Stop #N/A Value Returning In Sumproduct Formula

Jul 16, 2009

I have the following formula that sometimes returns an #N/A value. Is there a way I can stop that from happening?

View 9 Replies View Related

Formula Stop Displaying The Result

Apr 8, 2009

Workbook containing macros and formulas. The workbook will create worksheets automatically by macros and uses the same formulas in the worksheet. After creating the 10th worksheet these formulas stop displaying the result. They showed #N/A but in actual there supposed to have a values in each of the cells. By manually clicking the formula bar and pressing enter these values will appear.

View 9 Replies View Related

Prevent/Stop #DIV/0! Error In Formula

Apr 21, 2009

The attached file contains hypothetical data on a basketball player's success from the free throw line. It lists the number of free throws attempted and the number made and then divides the number made by the number attempted to calculate the free throw percentage. However, I keep getting a # DIV/0! error in the games free throws weren't taken. How do I write the code so that when the error occurs, a message is displayed to the effect that no percentage can be reported because no free throws were attempted by checking the cell in column D? How do I use the IsError function to figure this out?

View 2 Replies View Related

Stop Formula Viewing Without Protection

Dec 13, 2006

I am looking for the code to keep certain cells from being rested on, they can be clicked on but in order for you to view the formula in the cell you have to hold down the mouse button otherwise it will move you over to the cell next to it. I was using another worksheet a while ago and was trying to view the formulas but when I clicked on the cells holding them it would move me to the next cell, the only way I could view them was to hold the mouse button down as soon as I would release it it would be gone! I do not want to protect the sheet, if there is another way to hide formulas I am open to all ideas!

View 4 Replies View Related

Stop #N/A! In Index Match Formula

Aug 7, 2007

I am working on a formula that, eventually, I would like to be inserted into cells using VBA's FormulaArray Range function. Unfortunately, in its present state, the formula is too long and clumsy to meet the 255 character limit on that function. While I know that I can easily shorten the current formula with simple adjustments, such as using named ranges, I still am forced to ask whether the design of the formula itself could not be improved.

The heart of the formula is an Index/Match combination which will search if any line on the 'Archive' sheet meets the three criteria on the line specified in the 'Data Log' sheet. If so, it returns the value from a particular column of the matching row on the archive sheet. If not, it returns N/A.

=INDEX(Archive!$E$1:$E$1999, MATCH(1, IF(Archive!$B$1:$B$1999='Data Log'!$A2, IF(Archive!$A$1:$A$1999='Data Log'!$C2, IF(Archive!$D$1:$D$1999=1,1))),0))

In an effort to circumvent this unsightly N/A result I have attempted to use the ISNA function paired with an IF statement. The idea is that if the result is N/A then "Pending" will display instead. Otherwise the result of the indexing formula will display. This approach is outlined below:

=IF(ISNA(index function), "pending", index function)

The problem, of course, is that the indexing formula is rather long, and, with this approach, I am required to put its entire contents in the formula twice. I would greatly appreciate any help in streamlining this formula. Remember, the final goal is simply to search the 'Archive' sheet for a single line meeting three criteria and return a value from a fourth column, or if not return "pending".

View 5 Replies View Related

Stop Formula Auditing In 1 Workbook

Feb 15, 2008

I want to stop the formula auditing from working in one workbook without having to change settings in excel, is this possible?

View 3 Replies View Related

Totalling A Win Loss Column

Sep 27, 2009

I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?

View 9 Replies View Related

Calculate The Profit And Loss

Jul 20, 2007

I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Where:
Market position is either 1 or -1
Entry price is the price where the position is reversed.

For example:
H3=1 and I3=100.56, then
H4=-1 and I4=100.88, then
H5=1 and I5=100.53

I need to calculate the profit as follows:
I4 - I3 (because market position of 1 expects the price to go up) and then,
I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))

View 14 Replies View Related

Inventory And Profit/Loss

Feb 11, 2007

I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.

In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.

When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.

I would like to be able to keep track of inventory by vendor as well as profit by vendor.

Using the Consolidation PT does not seem to work.

I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.

I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?

View 11 Replies View Related

Stop Displaying Formula - Display Result

Jun 10, 2006

I entered a simple formula in a cell =B14*B16 but it won't calculate. The cell only displays the formula and not the result. tell me what I did wrong. I've attached a sample.

View 2 Replies View Related

Stop Macro From Changing Range In Formula

Jul 6, 2006

If I have a formula that uses cells that are currently blank - and then run a macro that puts data into those cells - the original formula cell ranges change - even if I put $ within the ranges.

i.e.

Cell A1 has formula = sumproduct(e1:e10>10)
Currently cells E1:E10 are empty

Run the macro

E1:E10 now has data in.

But formula in cell A1 now reads something like =sumproduct(E13:E23>10) or could read =sumproduct(Ref#>10)

View 9 Replies View Related

Tracking Weight Loss In Excel

Aug 2, 2008

How do I track my total weight loss in excel? Here is an example of what I am trying to do.

8/1/2008 228lbs
8/2/2008 225lbs
8/3/2008 223lbs

Total 5lbs

I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?

View 6 Replies View Related

Pivot: Gain/loss Over Time

Feb 12, 2009

I have a excel 2000 spreadsheet that has beginning and ending dates for certain services. I'm trying to tease out what our customer base has been doing over time in aggregate- i.e. how many customers subscribing to X in a given month/year.

The only way I've been able to do this in the past has been generating pivot charts of start dates (with 'start mo' and 'start year' busted out) as a positive count (+1), stop dates (with 'stop mo' and 'stop year' busted out) with a negative count (-1), then copying these tables into yet another page where I mash them together to get the 'flow' of our customer base over time and generating charts from there. Needless to say, it seems a whole lot more clunky than it should be, and I'm having a hard time searching the innarwebs for similar examples (even though I'm sure it's been done before). Example:

View 2 Replies View Related

Adding Icon Of Gain Or Loss

Dec 4, 2012

I have the data in

A1=30 and B1=40
A2=60 and B2=20

And I also 2 small Pictures ie picture1 and picture 2 which i insert in same excel sheet. I wanted to lookup C1 cell , if A1 is greater than B1 then Picture1 or vice versa.

View 1 Replies View Related

Formatting Dates To Won-loss Values

Jan 12, 2010

I currently copy/paste sports data from the web into MS Excel. One of the columns pasted contains "Won-Loss", which appears as '1-0' on the web. However, once the data is pasted, Excel identifies this field as a date and changes the value of '1-0' to 'Jan-'00. I would like to insert a new column and convert the field back to text (i.e. Jan-00 to 1-0). I have attempted several formulas, however no luck as of yet.

View 9 Replies View Related

VBA To Calculate Profit & Loss Of Each Race Bet

Feb 1, 2009

I had a member make the MACRO in the attached spreadsheet some time ago, when i run it on the spreadsheet a runtime mismatch error occurs. I cant find the mismatch.

View 4 Replies View Related

Formula To Stop Calculation If Redemption Status Is Not Yet To Be Dispatched

Mar 17, 2014

I have a file that tracks orders placed.

Column A gives the request date of the order, based on the TAT in column B the projected dispatch date gets calculated.

Once the order is dispatched and the date gets updated in column Z, the redemption status in column V gets updated as "Dispatched within TAT" or "Dispatched out of TAT" based on whether they have met the TAT or not. If the dispatch date is left blank then it shows "Yet to be dispatched".

My problem is that the column D does not stop calculation even after the order is dispatched. And therefore because of the conditional formatting turns red the minute the number is more than 0. So it is misleading.

View 3 Replies View Related

Stop/Prevent #VALUE In Formula Reference Error Cell

Sep 3, 2006

A1 refers to B1 which contains a number or #VALUE!

What could I use in A1 to return a number and if B1 = #VALUE!, a zero.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved