Set Up An Equation For An Unknown Variable In A Formula Equal To 0

Nov 1, 2008

how to set up an equation for an unknown variable in a formula equal to 0 (an Even-Money Probability Formula for Risk Arbitrage assessments).

The formula is as follows, where I am attempting to solve for Pe:

(Pe*EP)+(P2*EL)=0

Pe=even money probability (placed in column B1 in Excel)
EP=expected profit if deal closes (placed in column B2 in Excel)
P2=probability of deal breaking up (=1-Pe) (placed in column B3 in Excel)
EL=expected loss if deal breaks up (placed in column B4 in Excel)

B1*B2)+(B3*B4)=0

So, once again, I am attempting to solve for Pe, or column B1

B1=(-(1-B1)*B4)/B2

say, EP (or B2) =$2.00 and EL (or B4)=-$4.00

B1=(-(1-B1)*-4)/2

B1=2*(1-B1)

View 2 Replies


ADVERTISEMENT

Assigning A Variable To An Unknown Cell

Dec 27, 2006

I'm attempting to create a macro that will look at the total in column (K) and send an e-mail to two different addresses, depending on the amount. If the amount is over $10,000 then one address (over@macro.com) if under, then the other (under@macro.com)

My main problem is that I never know what cell the total is going to be in.

I currently have all quotes going back to the person that sent in the request, no matter what the amount. Their e-mail is in the sheet.

So what I think I need to do, is find the last cell in column K with data and assign it a variable. If that variable is over $10,000 then I can send the e-mail to a hard coded address. If it's over, I just use my original code.

View 9 Replies View Related

Set Variable To Unknown Last Used Cell In Known Column

Jun 24, 2008

I'm trying to define the last row in a column which is determined from a find command. It's quite a large sub, and the rest is working properly, so I'll just post the (still rather large) troubling section.

'startingPoint is declared as a range. Destbook, reporttarget and FullControlName all exist.

'this sets StartingPoint to a one cell range in a sheet named from a custom class in a workbook named from variable destbook

Set StartingPoint = DestBook.Sheets(ReportTarget.Name).Cells.Find(what:=FullControlName, searchorder:=xlByColumns)

WriteCol = StartingPoint.Column

'this changes the range to include the whole column of the existing range
Set StartingPoint = Range(StartingPoint.EntireColumn.Address).....................

View 2 Replies View Related

Calculate A Multi-variable Equation Based Off Of Data In A Table With Different Criteria

Jan 8, 2010

I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.

View 3 Replies View Related

Find Unknown Char Contained In A String And Store It In Variable

Jun 4, 2013

I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.

VB:
If tool.Worksheets("TRB Database").Cells(A, "R").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or
tool.Worksheets("TRB Database").Cells(A, "AA").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")

[Code] ....

That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".

VB: tool.Worksheets("TRB Database").Cells(row1, "D").Value = Chr(Asc(tool.Worksheets("TRB Database").Cells(A, "D").Value) + 1)

This is where I increase the value.

View 2 Replies View Related

Summing Across Unknown Range In Unknown Cell

Oct 26, 2009

I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!

I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.

Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.

Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?

Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).

Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!

View 14 Replies View Related

Unknown Cell Refernce In Formula

Nov 19, 2008

This may be an obvious one, I have received a spreadsheet with cell references or references to values that I am not familiar with, is anyone able to shed some light on how to interpret these references or at least find out where their source is within the spreadsheet?

View 3 Replies View Related

Make Variable Equal To Active Range In Sheet

Apr 2, 2014

I would like to run a calculation based on selected active range in sheet. I tried several methods and cannot make it work.

View 2 Replies View Related

Excel 2013 :: Formula Required To Remove Unknown Character And Extra Space From Name

Aug 19, 2014

i am trying to remove the unknown character and extra space from the name. Though i use formula as trim or proper(trim), it is not removing the Unknown character / extra space. I have attached the few name as sample. Formula to remove these Unknown character / extra space, double space, special character from selected cell?

Note : I am using ms office 2013

View 3 Replies View Related

If Equal To Or Less Than Formula

Oct 31, 2009

I am trying to set a scorecard up where it will give points depending on the % actual. For example:

if less than 60%, then 0,
if equal to or greater than 60% but less than 75%, then 1
if equal to greater than 75% but less than 80%, then 2 and so on.

I have been playing around with the example below, but can't seem to get it working.

[php]=IF(A1<60%,0,IF(A1>A1<75%,1,))[php]

Maybe it 's because I pickled my brains last night.

View 4 Replies View Related

IF Formula Based Around A Value It Does Not Equal

Nov 25, 2008

Is it possible to do an IF formula based around a value it does not equal?

eg I have one column, that is perforning a SUMIF providing that a certain cell is equal to a particular constant.

=IF([cell]=[value],SUMIF([range],[criteria],[sum_range]))

In the next column I want to do it the other way round so that:

=IF([cell] DOESNOT=[value],SUMIF([range],[criteria],[sum_range]))

Is it just a simple symbol??

View 2 Replies View Related

Formula Equaling Zero Is Not Equal To 0

Dec 14, 2009

I have a simple formula which subtracts one cell from another. I then have an if and statement which checks if that cell is equal to zero. If so, continue processing.

The problem is that the formula always reads the cell as not equal to zero although the result of the formula contained in the cell does result in zero.

View 5 Replies View Related

Get Formula In A1 Without Equal Sign?

Jul 11, 2012

i want formula in b1 to get the formula in a1 without Equal Sign

A1=(A2*A3)+2

Reults B1 should be (A2*A3)+2

View 4 Replies View Related

Not Equal In SUMIF Formula

Feb 5, 2013

I am new to using SUMIF. I successfully wrote a formula telling it to sum one column for each row in another column where it equal "Forecast*". Now, I want to write a formula that says to sum a column for everything in the other column that does not equal "Forecast*". I tried using , but either it did not work, or I did not use the right syntax.

View 5 Replies View Related

Greater Than And Less Than Or Equal To Formula

Jun 16, 2009

I need a formula that looks at the total in H40 and if the number is between 32 and 40 I need it to return the number then if the number exceds forty I need to multiply the overage by 1.5 and add it to the 8 for a total of 11.

I think it would be something like:

SUMIf(H40>"32",

View 9 Replies View Related

Formula To Highlight If 3 Numbers Are Not Equal

Apr 14, 2014

I have three workers for which I am counting the data from using a countas formula. The cells are A1,B1,C1 and I want to highlight in another formula to the right if these three values do not equal each other, I have tried and if formula referencing each of the combinations of the compares however it does not work in the desired fashion.

View 3 Replies View Related

SUMPRODUCT Formula To Equal Blank Value

Nov 14, 2006

I have a code which will delete all rows where the value in column C is blank. However my formula is column C is the following:

=SUMPRODUCT(--(ENERGY!$B$5:$B$147=B15),--(ENERGY!$E$5:$E$147=A15),ENERGY!$C$5:$C$147)

Is there a way to change this formula so that if a value is not returned, I get a blank cell?

View 9 Replies View Related

If FORMULA: If The Mark Is Less Than Or Equal To 20, The Answer Is AVERAGE

May 19, 2009

I would like to use IF function, to get the answer, if the mark is less than or equal to 20, the answer is AVERAGE, if the mark is between 21 and 25 the asnwer is GOOD, if the mark is between 26 and 30 the asnswe is VERY GOOD, if the mark is above 30 then the asnwer must be EXCELLENT.

View 4 Replies View Related

Formula / Calculate Number Of Days Between 2 Dates That Equal A Given Value

Mar 19, 2014

I have two date columns, a start date and end date. Another columns tells me the number of days between these two columns. It may return 45 days which actually straddle 3 different months.

My aim is to identify between this date range how many days in Apr 14. Then in the next column howmany days in May 14 etc

So on the spreadsheet Id have 12 columns for each month of the year. in Apr 14 for each row between the given date range I want it to tell me how many days are actually in April. Id then copy that formula into the May 14 column.

View 1 Replies View Related

Days Late Formula (result To Equal Either Zero Or Blank)

Oct 1, 2008

I have a formula in Excel2003 that is working fine except for when D12 is blank. When D12 is blank I want the result to equal either zero or blank.

=IF(ISBLANK(E12),NOW()-D12,E12-D12)

View 2 Replies View Related

Simple Date Comparison Formula - When Two Dates Are Equal To Each Other

Feb 10, 2014

Report I generate once a month that checks to see if tasks are done on or before their due date. Sometimes it works, and others it doesn't...

The basic formula is: [Code] .....

If they closed their task by or before the due date, the condition should be true and it should output "Compliant" beside their task. Otherwise their "Over Due".

The forumla works except when they close their task ON the due date. No matter how I tweak the forumla, it thinks the condition is false as if it cannot tell when the two dates match.

View 1 Replies View Related

Add Formula To Cell To Equal Product Of 2 Other Cells Via Macro Code

Apr 18, 2008

I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors

Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"

When I record a macro it gives me this, but again, I need the last cell in terms of "T"

ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"

View 3 Replies View Related

Values Which Are Equal Not Treated Equal

Jul 5, 2007

I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.

View 2 Replies View Related

Macro To Print Number Of Copies Equal To Output Of Formula In Specific Cell?

Apr 21, 2014

I can assign a number of copies to print by adjusting ActiveWindow.SelectedSheets.PrintOut Copies:= , but what I want it to have the number of copies auto adjust to the value of a cell that contains a formula. I was hoping it would something as simple as Copies:=cell but no luck...

View 2 Replies View Related

Excel 2010 :: Tables - SUMIFS Formula With Structured References And Greater Than Or Equal To

Nov 13, 2013

SUMIFS with less than & more than

I found this thread which works for normal ranges

I want to Sum for Values Greater than a specific date in my formula.

This first one works for a specific date (Relevant reference in red), so say 01/01/2014

Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

Code:

To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.

=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
">="Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

View 4 Replies View Related

The Logic Of OR: IF Statement That Will Evaluate Whether A Variable Is Equal To Any Of The Three Punctuation Marks ".", "?", Or "!"

Apr 16, 2009

tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:

View 2 Replies View Related

Equation Behind The Function

May 9, 2006

How do I get to see the equation behind Excel Functions? For instance, the
result of pmt() is not making sense to me, I'd like to see what this function
is doing and how it is calculating its value. Trial and Error is rather
time-consuming and tedious.

View 13 Replies View Related

Setting Up A Log Equation

Oct 9, 2009

It looks like this (log(X/Y))/log2. The X and Y are celldirections (E2 or F4 and so on). How do i setup that formula in excell?

View 2 Replies View Related

1 Equation For 4 Variables

Jul 24, 2012

Can I use excel to get make an equation for 4 variables (x,y,z,w)

E.g.

a,b,c,d,3,f,g,h,i, would be constants

w = ax + by + cz + dx^2 + ey^2 + fz^2 + gxy + hyz + iyz

I tried regression but the equation looks like

w = ax + by + cz + c

Is it possible on ay other software e.g MATLAB

View 9 Replies View Related

SUM IF- Excluded From The Equation

Oct 18, 2007

I have the following formula.

=SUMIF(hfx!A:A,"123456789",hfx!J:J)

very simple.

for the totals in J, if there is a (-) negative number, i would like that excluided from the equation.

View 9 Replies View Related







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