I want to accomplish a few things in one cell. The basics are this:
I want to pull the lowest numbers from a given sample. The sample needs to have a time component (10 lowest out of the most recent 20 results). Those numbers are to be averaged. That average is to be multiplied by .96, then that final yield needs to be truncated to 1 decimal.
Now, I'm not sure this is possible or not. But, if it is, I want to also select the amount of numbers I pull from the sample to average to be based on the sample size itself. For example, if there are 10 results in the sample size, I only want to average the lowest 3 of the 10.
Is it possible to formulate all of these rules into one cell?
I had the first and third argument working properly but am running into difficulty when I tried to enter the middle formula.
If A1 + B1 = 0 then "0" OR
If A1 = 0 AND B1 is greater than 0 then give B1's value times .01 If neither of those are true Then A1 minus B1, divide the result by A1 and then multiply it by negative 1.
=IF((A1+B1=0),("0%")*OR,IF((A1=0 and B1>0),(B1*0.01),((A1-B1)/AI*-1)))
I need a formula that will determine if a line is active or expired by first looking at the category and then looking at the number of days. (While still incorporating the ISBLANK section of the current formula.)
For Example: If the category is either FWW or FWW Ext then the line will expire after 365 days. If not, then it will expire after 180 days.
I have 2 cells on the same page of the worksheet that I would like to have a popup box flag if the user has entered a number over 100%. In this example the value is hard coded in cell A41 as 100% and can not be changed by the user. The entry cells are A14 and A15. The code works fine if I comment out one of the Worksheet_Change statements.
Is there a way to have 2 worksheet_Change statements on the same sheet or do I have to combine the if statement. My attempts at this point to combine the if statements have not worked.
Private Sub Worksheet_Change(ByVal Target As Range) 'MsgBox for the % value range READS Select Case Target.Address Case "$H$14" If Range("H14").Value > Range("A41").Value Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True MsgBox "This value cannot exceed 100%.", vbCritical, "ERROR" End If Exit Sub Case Else Exit Sub End Select End Sub
The code I have below is relevant to this spreadsheet Copy of Invoice test.xlsm
In the code below I marked the script that has issues in red. The code takes the last row and the last column and works from there. This works fine with single commands, however, when I take a cell (based off the last row and the last column) and try to give that cell multiple commands excel sends back an error. When I use this multiple command (see part of code that is marked in red) for a range that is not based off the last column it works fine.
I would like to make an IF-formula in excel. The problem is that i would like to to it for 12 different commands. i.e "IF(A1=14;243;"") but for 12 commands I saw that the in-built IF function in excel only manage 7 commands, is this true? And how do I write the formula,
I am not sure if the VLookup or the If function will be the best way to perform this tast but I think it is they way I would want to go.
I have attached a sample workbook of what I am working with. Please refer to it.
On sheet 1 I named the table array Ear_Tag.
This is information that is provided to me from the farmers.
Note that there are sometimes more then 1 pig with the same tag number. The only way to tell them apart is by the Test Date.
On Sheet 2 Is where I record the test results.
I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.
Trying the following syntax and I do not get any value in the cell if either of the conditions are true. Pretty sure I'm nesting the functions incorrectly. Maybe I should use Index/Match? =IF(AND(E24="SI",F24="SI"),"ER2","",)IF(AND(E24="sn",F24="sn"),"er2","")
I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5?
How I use the Private Subs for Workbook_AddInInstall and Workbook_AddInUninstall with my addins and confirm it is nececessary to use them?
For 2 weeks, I have not been able to get my addins to activate and load into the AddIns tab. I think these missing Subs are why. Ironically, Every example on how to create addins never mention this step or it being necessary. I've even looked in Macros book and it does not mention anything about needing these subs in the Creating AddIns section so they will show up in the AddIns Tab.
How I can get my Addins in the tab and not the Quick Launch menu
I would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.
In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.
I just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".
Is there any way that i could open and enter commands in ms dos controlling from excel vba code?, like, say i wanted to change the current directory in msdos from d: to c:, is there anyway that i could do it from excel vba?
I have a template sheet where the only thing the user need to do is fill in all the appropriate cells and the click the button that says SAVE to save the file to the right folder and file name.
I want to Grey out the ribbon completely so that user can even change the font or any thing else for that matter. The sheet and workbook is protected. Need a way to stop any unwanted tinkering.
Sub elaseval() For each cell in Range(“B7:B50”) Range("cell.Value").Select Selection.Copy Sheets("Input").Select Range("$E$7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("Sensitivity").Select Range("C” & cell.Row : ”T” & cell.Row").Select Selection.Copy Range("C" & Cell.Row).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Next Cell End Sub
I am trying to apply this set of commands to each cell in the range B7:B50. For each of these cells, I need "cell.Value" to refer to the value in the respective cell and "cell.Row" to represent the row of the cell that is currently being used.
I am trying to remove File Commands from this specific workbook and force save and send functions through command buttons.
This is the code I tried in the workbook module to remove file commands(Excel 2003): __________ Sub Workbook_Open() Dim a As CommandBar For Each a In Application.CommandBars Application.CommandBars(a.Name).Enabled = False Next Application.CommandBars("File").Enabled = False Application.CommandBars("Edit").Enabled = False Application.CommandBars("View").Enabled = False........
I have made an Excel sheet that uses ActiveX controls (Option Box and Check Box). I have included a couple of screen caps at the bottom of this post to better explain the layout. Here are the specifics:
There is one Option Box Group labeled "InputOptionGroup" with choices labeled: "FirstOption", "SecondOption", "RefundOption" and "NoneOption".
Adjacent to the Option Box is a series of Check Boxes that correspond to the choice made in the Option Box with the exception of "NoneOption". The Check Boxes are labeled "FirstCheck", "SecondCheck" and "RefundCheck". Each Check Box object belongs to the group labeled "OutputCheckGroup".
I do know that the value of the Option Box choices and Check Boxes can be either True or False depending on if it has been chosen or marked, but I do not know how to incorporate these values into the action script. With that being said, here's what I'm trying to accomplish:
...when "FirstOption" is chosen, clear values of all Check Boxes and place a check mark in "FirstCheck". ...when "SecondOption" is chosen, clear values of all Check Boxes and place a check mark in "SecondCheck". ...when "RefundOption" is chosen, clear values of all Check Boxes and place a check mark in "RefundCheck". ...when "NoneOption" is chosen, clear values of all Check Boxes (no furthur action).
Also, there are a couple more requests:Is there a way to lock the Check Boxes from user input? I want the Check Box values to be altered only by the choice in the Option Box. Take the "GPIN" field, the one on the right is locked from user input. This particular cell references user input from the "GPIN" field on the left. Is there a way to return a blank value when the input field is blank insted of returning a "0"? If you notice, all fields on the right reference blank values as "0". In the left "Amount" field, I need to display a "$" followed by the value in the input "Amount" field
I need to disable the "save as" file menu command when a user opens a shared document. Upon exit I want to enable the save as so that other opened worksheets are not affected. This code works: CommandBars("File").Controls("Save As...").Enabled = False
My problem is this. I can activate this command by selecting run in the VB editor. But I need for this action to be automatic when a user opens the worksheet. I did this in the editor:
Under general
Sub dISABLESAVEAS() CommandBars("File").Controls("Save As...").Enabled = False End
Sub WORKSHEET_BEFORECLOSE() CommandBars("File").Controls("Save As...").Enabled = True End
I tried using this array formula but it has an error in it and I can't figure out the error. =SUM(IF(Tracking!$C$2:$C$5000=$B$4,IF((Tracking!$D$2:$D$5000="15-M Eval")+(Tracking!$D$2:$D$5000="15-M TIC")+(Tracking!$D$2:$D$5000="Misd CRT"),IF(Tracking!$E$2:$E$5000=$A6,IF(Tracking!$J$2:$J$5000>=VALUE($B$1),IF(Tracking!$J$2:$J$5000<=V ALUE($B$2),1,0)))))) (I attached the workbook. In the CSTE worksheet, I didn't use control-shift-enter so you could see the formula.)
What I'm trying to do is come up with a formula that will sum if either of these three Legal Status types (15-M Eval, 15-M TIC, and Misd CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. The formula works if I just want to find one Legal Status type. I also will want to come up with a formula that will sum if either of these five Legal Status types (15/30-F Eval, 15-F TIC, 1/90 CRT, 2/90 CRT, and 180 CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. I tried researching if I could define a name that would sum these 3 Legal Status types but that didn't work either.
View attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands.
I have the code that accompanies the attached spreadsheet model that locks the copy, paste, cut, double click and so on, with the right mouse button and shortcut keys, however, the toolbar excel these functions still remain active. How well I block access to the toolbar.
I need to transfer two commands to command prompt. The first one is always the same and it calls a command line of another application (BEAM). The second command is for the BEAM command line. It is taken from an Excel cell. The code I managed to write is transfering only the first command, probably because after execution of the first command command prompt is getting BEAM command line. I
