I would like to incorporate a series of if statements in column D of my macro that looks at the labels in column C. If the label is 10 characters long, it returns the full 10 characters. If the label is 11 characters long, it returns the leftmost 10 characters.
What syntax would work for this?
I have this nested if statement, but my last IF statement needs to incorporate another component.
View 2 Replies View RelatedI am trying to incorporate several IF statements into a single formula:
=IF(AB5<20,"LR",IF(AND(AB5<30,AB5>25),"C",IF(T5<>"","CLEAR",IF(AND(AB5<240,AB5>40,AA5=1),"B-T1-F10",""))))
What I am trying to achieve with this formula is:
- insert LR if AB5 is less than 20;
- insert C if AB5 is less than 30 but more than 25;
- if neither of the above is true, insert CLEAR if T5 contains something and if not insert B-T1-F10 if AB5 is between 240 and 40 and AA5 = 1.
The problem I have is that the B-T1-F10 is not being inserted where the other criteria are not met so I think I have not integrated the statements correctly?
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
I have the following worksheet ,named "Calendar", to make an annual calendar and I'm wondering if there's a way to use it to make a calendar in worksheet named "NovemberDaily". On the worksheet named "NovemberDaily" I want to put in cells C14, G14, K14, O14, S14, W14, and AA14 "11/1/2007" if the weekday of the week matches the date"11/1/2007". Is this possible or is there an easier way of accomplishing this. I'm needing the date (e.g. 11/1/2007) in the cells mentioned because it ties into another formula that I'm using later in the worksheet.
View 32 Replies View RelatedI have a macro that create the page header (and footer), which uses a jpg image/logo from my computer. How can I incorporate that into the code so that the macro/image will work properly if not run on my computer?
View 1 Replies View RelatedI am using a script within excel as part of this script I want create a new folder to store information
Code:
ChDir "C:Documents and SettingsAdminDesktopMIKEPAYROLL2011-12"
However I want to use a variable called "NEWYEAR" which holds the 2011-12 part
For example this part stays the same
ChDir "C:Documents and SettingsAdminDesktopMIKEPAYROLL
folder 2011-12 held in variable NEWYEAR may or maynot exist.
if the folder exists I want to be able to use the folder, however if it doesn't I need to create it.
The problem is I am not sure how to incorporate the variable into the path.
Last Name
First NameSales CodeDepartmentSalesHours WorkedBase PayCommission RateAllbaughJoshuaAMMen's$ 4,000.00 8.00188.0072
I created this formula to show commission but it doesn't incorporate the array below.
How can I incorporate a vlookup into the formula above showing this array of info?
Sales CodeSales TitleHourly Wage QuotaCommision RateAMAssistant Manager$23.50 $200.00 0.03PT1Sales Assistant$10.50 $100.00 0.01PT2Sales Partner$11.75 $125.00 0.015S1Sales Associate$13.50 $150.00 0.02S2Sales Consultant$15.00 $175.00 0.025
Is there a way to include an if then else function to an average formula that will skip cells with zero?
I went into better detail in the attached example.
I need to often enter a formula such as:
=SUMIF(H1:H500,"=a",G1:G500)
but, I need to change the variable 'a' to whatever letter I need.
I wrote the following:
y = InputBox("Enter letter")
So, how do I incorporate the 'InputBox' result in the formula?
If I use =SUMIF(H1:H500,""=x"",G1:G500) I just get 'x' as a result, not the letter I input.
I have have sumproduct formula that works well, however I need a section of it to use a condition.
"=SUMPRODUCT(--(('Input Sheet'!$A$10:$A$20009=3)+('Input Sheet'!$B$10:$B$20009=1)+('Input Sheet'!$C$10:$C$20009=1)=3),--('Input Sheet'!$L$10:$L$20009>=G21),--('Input Sheet'!$L$10:$L$20009<=J21))"
The first section ('Input Sheet'!$A$10:$A$20009=3) looks at column of data and checks if any of the data says 3, however I need the formula to look at the column of data and determine if the figure is =>3 and also =<5.
I have the following formula: ...................
and excel is saying I used too many characters and to use the concatenate function to do so, but I have no idea how.
I wanted to return text rather than a '0' in the case that all the original IF statements were false, I threw another IF statement in there and said if the original is equal to '0', then return "-". If not, then run through the original again (I pasted the original again after the "-").
I want to search the selected range for a variable value (calculated previously in the sub) and if it finds the value I want the sub to do some things. If it's not in the range I want it do something else.
Here is the relevant section for what I have:
[Code] ......
This works and cuts the value I'm looking for if it finds a value in the range. The problem is it's not in the range I don't know how to tell it to follow other instructions. I tried the "iserror" with an in statement, but it said the range was not set. Intellisense isn't working and I don't really know how to use the .find method ...
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
I'm trying to incorporate a Cell result in my header (the one you select from "Page Setup"). Is this possible? I would like to avoid macro if possible and prefer some type of formula.
Windows XP with Excel 2007
How to incorporate a calculated field for a sub group.
Here's what the data in the table looks like.
Competitive
Managed
ID
A
B
C
A
B
C
[Code] .....
I am looking to get 3 calculated fields onto the Right hand side that would essentially be the following Formula
=IF(L4"",IFERROR(D4/H4,"n/a"),"")
Thus,
1st Column
=IF(Competitive(A)"",IFERROR(Competitive(A)/Managed(A),"n/a"),"")
2nd Column
=IF(Competitive(B)"",IFERROR(Competitive(B)/Managed(B),"n/a"),"")
3rd Column
=IF(Competitive(C)"",IFERROR(Competitive(C)/Managed(C),"n/a"),"")
The other option would be how to set up conditional formatting to always take on the appearance of Column K of the pivot table. Thus, this calculated section would always appear as though a part of the pivot table and would adjust based on any pivot table items being expanded and collapsed.
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:
I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)
How do I write that kind of formula?
I would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.
The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.
Excel version: Excel 2010
OS: Windows 7
I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....
View 9 Replies View RelatedI searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple.
Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col).
I know that you can just write a formula in each cell like...
b1 will be =sum(a1+5)
b2 will be =sum(a2+5)
... but is there a shortcut so that you don't have to write out the actual cell address for each one?
Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like...
b1 will be =sum(left+5)
b2 will be =sum(left+5)
... where "left" represents whatever value is in the cell to the left of that particular cell?
I have a spreadsheet with a customers information and various parts we make for them. I need to be able to take this information and incorporate it into a label format. I need something quick and easy as there can be 150 parts per customer
Customer: ABCD
Customer PO: 12345
Part Number Quantity
AB 1
** 15
EF 22
GH 14
and I need it to output:
CUST: ABCD PART: AB
PO: 12345 QTY: 1
CUST: ABCD PART: **
PO: 12345 QTY: 15
CUST: ABCD PART: EF
PO: 12345 QTY: 22
CUST: ABCD PART: GH
PO: 12345 QTY: 14
My formula is not working correctly; not sure what to do. I use this same formula with numbers and it seem to work, but only have 8 IF statements as well. This example below doesn't even work with only 8 IF statements.
--------------------------------------------------
What I am saying below is = If E3 = a swat team (i.e. EO-Deal Processing-Prescreen) then please enter "Chumbley" and so on. For anything else put an na or n/a for blanks or swat teams that I haven't lised in the criteria.
---------------------------------------------------
=IF(E3="EO-Deal Processing-Prescreen",Chumbley,IF(E3="EO-Deal Processing-DocGen",Chumbley,IF(E3="EO-Deal Processing-Events",Chumbley,IF(E3="EO-Deal Processing-Triage",Junk,IF(E3="EO-Deal Processing-Processing",Queck,IF(E3="EO-Deal Processing-Calcs",Doyle,IF(E3="EO-Deal Processing-Closing",Terry,IF(E3="EO-Deal Processing-D11B",Blazier,IF(E3="EO-Deal Processing-Isolated UI",James,na)))))))))
a = Range("d1").CurrentRegion.Resize(,2).Value
and
Range("G1").Resize(n,2).Value = b
what do the above two lines of code do?
