I have a formula and in it is a square root, i want the formula inside the square root always to be positive so i get a result.
Example:
(A1)
-1
(A2)
0
(A3)
-2
(C1)
=(A1)^0.25
(C2)
=(A2)^0.25
(C3)
=(A3)^0.25
Now i only get a result from the last two, but i want to use the modulus function so that instead of a maths error i get 1 for the first one, how do i do this?
Square Root Spiral Function Graph. i was looking for vba code to graph the Square Root Spiral from Pythagorus in Excel...?
View 8 Replies View RelatedI need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger. So far Iv been doing
A1 = 2
A2 = AND(RIGHT(A1,1)=INT(A1))
True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have 49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND).
i am trying to use the square root formula to work out the top length of the picture on the userform from the numbers i have in the textbox's.
It is basically a 10x5 rectangle and i need to find the diagonal length
which should be 11.18 but it is returning 10
is there a way to get a formula to count a zero if it would normally return a negative value?
example
result = 100
target =300
result = -200
but i want it to show as zero
does this make sense?
i'll bet its really simple but i'm stuck
Is there a way to have a formula return only positive numbers? basically if the result is of this calucation (B4-(B3*B6)-(B6/2)*0.125) is >0, then return the result, if not return "" (or 0).
View 6 Replies View RelatedI am trying to make a column show the result of a formula as positive number only. Right now the column subtracts two other cells and displays the result. I am trying to make it show all the results, positive or negative as positive.
View 3 Replies View RelatedI have a column of numbers such as
1001150
1001124
2224445
I need add a period in the following locations
10011.50
10011.24
22244.45
I figured this out using a format rule of
#.##
I then need to make the numbers negative so I did
-#.##
but this doesn't "stick", if I filter the numbers by negative numbers, none of them show up. So how do I make the formatting actually become the numbers? Auto Merged Post Until 24 Hrs Passes;After doing some more research I found the "precision as displayed" option. I can't find this option on Excel 2007, but I moved the files into 2003 and the option doesn't do anything. It is not permanently changing the column that I have added the formatting too.
I have calculated the t values then i have three tables for 3 confidence levels, 0.01, 0.05,0.1 in which I compare the t value with the value from the
t-test table. If the t value is larger it returns a 1, if not a 0. This works fine.
To summarise the information I have a final table which I wish to return the highest significance of the given t value.
I am using this
=IF((G53=1),0.01,(IF(G44=1,0.05,(IF(G35=10,0.05,"none")))))
the problem I have is it always returns 0.01 even if g53=0. If i remove the formula from g53 and replace it with a 0, it returns 0.05 instead (even if g44 is retruning a 0).
I have considered copy paste values and doing this but that rather removes the automation aspect of the spreadsheet if I need to make adjustments.
I'm trying to make a "weight-based" spreadsheet. I want to show a difference between numbers so if an item lost 5 lbs, there would be a "-5" and it would be in green font showing good.
For example, a box weighs 20 lbs and then drops down to 15 lbs. This would be a good result. So I would have 20 lbs in A4 and 15 lbs in A6 and I want to show the difference in A8. However, since I want the box to lose weight, I want it to show a negative value of "-5" in green font meaning it is good. And vice versa, I want it to show "+3" in red font if the box gained weight.
I can't really figure out how to get the signs working properly. I'm assuming to use an "if" function in where if the difference is a loss, show the negative sign and have it in green font. And if the difference is a gain, show the positive sign in red font.
I have a user defined function that somehow keeps interfering with my other module. I use f8 to step through module 1, but halfway through the process it skips to module 2 where I have the below user defined function...
View 9 Replies View RelatedI am stepping through a sub in module one that for some reason keeps jumping to a user defined function in another module. I've checked and and the function isn't being called.
View 9 Replies View RelatedI have made my own excel function that my users needs every day. it works just fine, however if you do not know the required argument for this function, you couldn't use it. How can i prompt my users for the right order of the argument when they type the function in excel?. example is when you type the Function "=left(" you will be prompted by excel of the correct arguments.
View 3 Replies View RelatedI have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?
View 5 Replies View Relatedcount if fuction on positive and negative number in the attached excel sheet. I would like to count how many numbers are positive and negative in the A column of the attached excel sheet.
View 4 Replies View RelatedAfter all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?
View 2 Replies View RelatedLet's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?
View 1 Replies View Related i got a question how i can make a msgbox in one module with 'Yes' linking to a different module.
such as: this is located in module2
Code:
If MsgBox("Do you want to activate module1?" & vbCr & _
"" _
, vbYesNo, "Choose") = vbNo Then Exit Sub
i want that vbYesNo different
No as in Exit Sub
yes as in activate Module1
I created a trendline and associated equation for some data plotted against Time on the x-axis displayed in MM/DD/YY format. The date for the data ranges from 3/27/06 thru 11/10/08.
The trendline equation is
F(x)=-6E-13x^5+1E-07x^4-.0087*x^3+329.1*x^2-6E+06*x+5E+10
The data actually crosses the y=0 line around 11/5/07, which is the data root
When I use Goal Seek, I got a zero root solution of 28154.98. When I convert this to the date format, it converts to a date in 1981 which is not correct. I tried a range of initial guesses but they all converge to the same number.
How set up root sum calculations.
A1 has 0
B1 has 0
C1 has 9
on D1 would like to show the root sum of 009 (which is 9). What is the best formula for cell D1?
A1:C1 will have random input numbers between 0-9.
I have a long url and I need only the root domain in the url.
for example:
[URL] .....
[URL] .....
[URL] .....
[URL] .....
[URL] .....
if the code is 110 add together all amounts attached to that code and sum them in a new cell. Perfect. Now however there is an added level of complexity and i need to figure out how to nest an IF statement into a =sumif() function so that code amounts are summed into their root code, for example the root code of 110, 120, 130 is 100.
So basically i want a function that will sum the specific code (110) and then sum together that amount under the root code (100). Here is an example. The amounts for the lower level codes have been summed but i need the high level codes to be summed together.
is it possible to type a document name in a cell,then run a code that searches a root dir and sub folders, find the existing document and open it?? for example
cell: "D20"
filename: DYAZES-001
root dir: "I:IsolationDataBaseIsolationProcedures
subfolders: DryMillA, DryMillB, Despatch, Maintenance
I have to run a daily report from winxp or win2k and need to change the host files a few times for access.
I tried to use %systemroot% in the file path, but for some reason vba doesn't like it.
Here's the snippet I'm using for copying over the host file based on user choice: (the thing works if I change to the actual system root path (C: etc...)
Private Sub CommandButton1_Click()
Select Case ComboBox1
Case "- Choose -"
MsgBox ("This is not a choice")
Case "BTR"
What i have at the moment is a module that contains code where i call a different module that i use as a procedure.
Module1
Code:
If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then
If Sheet1.Range("K4") = "" Then
MsgBox "Please check 06:00 tasks not done yet!"
Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")"
If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then
Run "EmailProSheet"
End If
End If
End If
EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?
Module3
Code:
MsgBox Cell
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
[Code]........
As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?
I want to run a macro if the result of an IF function is true.
E.g. cell J55 contains =IF(H55>I55,"Goodbye","")
H55 contains =NOW()
I55 contains =DATE(2012,12,31)
Now by changing the dates etc. it prints Goodbye.... simple enough, but what I want is for it to run a macro that selects a range from the previous year's figures and simply changes the fill colour on the range. The macro for this also works fine.
I have a huge worksheet that contains four character payer code in column D for example: 9081, M897, 0235. I am looking for a function that will show the result in column AD
IF payer code in column D starts with a 9 (9???) - GOVT
IF payer code in column D starts with a 7 (7???) or M (M???) - MNGD
IF payer code in column D starts with a 2 or 0 (zero) COMM
IF payer code in column D starts with either Z, I, C - PTR
I tried IF function, but it didnt work for me.
Can this Formula work? Where if c2<e5 then its value is a number. The formula below is what i tried but it didnt work
=IF((C2<E5);"c5=value=f5")
I've created a Database using excel which feeds data to a pivot table I created based on it. My objective is to create a new item in this pivot table (Pivot Table > Formulas > Calculated Item) which calculates the average data for the past 6 months.
The formula I used in order to get a result is this:
=IF(((May+June+July+August+September+October)/6)=0;;(May+June+July+August+September+October)/6)
and the problem is that the answer is 0 when there is no data for all 6 months (for example, there is a customer who hasn't baught anything during the past 6 months), the pivot table displays 0, instead of not showing this customer at all. My goal is for the IF function not to return any data if the result is 0, so that the pivot table doesn't show items with no data.
I also tried:
=IF(((May+June+July+August+September+October)/6)=0;"";(May+June+July+August+September+October)/6)
and also using ;" ";
but these formulas return #VALUE! in the pivot table, given that the Data is "SUM OF Sales" and "" is not a number.
How can I get the formula not to show anything at all?
In addition to this, I was wondering if it is possible to make this formula more automated, so that it calculates the average of the past 6 months, taking into account some type of reference month (rather than having to edit the formula each time a new month is analyzed).
I have a simple/dumb question... How do you "capture" a value that is returned by a custom function.
I have tried searching the forums for this & I know that I should by all rights know how to do this by now... but I just can't figure it out.
Here is a Function that I copied from Ktrasler in this thread:
Week Numbers
Public Function MyWeek(DateArg As Date) As Byte
Const BaseDate = "30/12/2001"
DateArg = CDate(DateArg) - (Weekday(DateArg) - 1)
MyWeek = Int(((DateArg - CDate(BaseDate)) / 7) Mod 52)
If MyWeek = 0 Then MyWeek = 52
End Function
I know how to pass variables to Functions, and tried this one out & it worked splendidly for my needs, but how do I take the value of "MyWeek" and use it in the subroutine that I am calling it from?