Free Microsoft Excel 2013 Quick Reference

Understanding the logic and criteria of Array formulas

Hi all

I want to understand the logic of array formulas that how they work. infact
I want to know the crieteria behind making the arrays.
I used some arrays and made some as well (like SUM IF array) but the main
thing which I want to know is the crieteria to make arrays and what Excel
functions we can use in arrays and what not? e.g I have seen mainly SUM and
IF functions which are used in arrays but many other functions do not work
when tried to used in arrays.
I have read many articles including microsoft's articles on array formula,
all article contain some formulas (basic and advanced) but the logic and
basic crieteria to make arrays is not explained.

can you please guide me to make the arrays (basic and with nested functions
as well.)


How does AND work in array formulas?

I want to count the number of positive numbers in a named range that meet certain criteria, but my formula isn’t working as I expect. In the formulas below, the named range “SaleAmount” contains one text value, 56 positive numbers, and several hundred empty cells.

This array formula returns the value 56, which is correct:
{=COUNT(IF(SaleAmount>0,SaleAmount,FALSE))}

I think the following array formula should also return 56, but the value returned is 1.
{=COUNT(IF(AND(SaleAmount>0),SaleAmount,FALSE))}
Why does this array formula return the value 1 instead of 56?

This array formula should return the value 20, but also returns the value of 1.
=COUNT(IF(AND(SaleAmount>0,SaleAmount<100000),SaleAmount,FALSE))
Why does this array formula return the value 1 instead of 20 ?

Can anyone suggest what I am doing wrong?
Thanks in advance for any explanation.

Hi,
I need a formula to count disctints like =
SUM(1/COUNTIF(C26:C46,C26:C46) but I need conditionals in the range and
criteria of countif.
I've tried this, but its not working.
=+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)
Thanks

First let me say i'm horriable with explaining things. So please ask questions if you don't understand.
I have a code that works great for me the only thing is i really want it to highlight the cell to the left and right of the one being highlighted.

It's a long code as i did it for every line. (not sure how to use the loop funtion) but here is just line for the code. So i would want Cell C6 and E6 to also be highlighted not just D6 .. the tricky part is there is no value of importance on the cells i'm tring to highlight.

Range("AB6,Y6,V6,S6,P6,M6,J6,G6").Select
Range("G6").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=$D$6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

I attached a photo of what the cells normally look like after this code is ran..
that's Cell 6

the image cell 6 b - is how i want it too look.
any suggestions

Hi guys.. I am new to excel macros.. hence require help in writing macros for the following checks.. I am also attaching the sample workbook below..

1. Validation check_1: Check the length and value of “Customer#/ADU”.

The allowed conditions are listed below. If error conditions are raised show the Error message to the “Result” column line.
Length Value Error Message Customer# 6byte “No check” ERROR:Invalid Customer# ADU 1byte 2/3/7 ERROR:Invalid ADU I have used this snippet but it doesn't seem to work..

	VB:
	
lastrow = Worksheets("Main data").Range("A" & Rows.Count).End(xlUp).Row 
mx = 6 
For i = 9 To lastrow 
    If Len("B" & i) < mx Then 
        Range("G" & i).Value = "ERROR:Invalid Customer#" 
        Enf If 
    Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
2. Validation Check_2: Check the “Data Value”
Check the length of “Data Value” filed. Get the information of correct length of each value from “Length” field of “Table information”. “Blank” is also allowed for exceptionally condition..
If error condition is raised, show the Error message” ERROR: Invalid Data value” to the “Result line” field on “Main data” sheet.

Regards

Sravanthi

Hello

Does any one know how to add a word or number at the front and end of the column
example

BAM-022311 BAM-022411 BAM-050200P2 TO add WEBOE in the front

WEBOEBAM-022311 WEBOEBAM-022411 WEBOEBAM-050200P2 WEBOEBAM-051900P2

My next one I need help in is place a a number at the end but have it go up each row

example

WEB00000
WEB00000
WEB00000

TO
WEB000001
WEB000002
WEB000003
WEB000004

and so on.

Hi all,

I have one column of wordings below

Column A wordings
dictionary
book
telephone
computer
mouse
etc

If I want it to add something at the beginning and end of wordings, how do I code the marco?

Column A wordings
((dictionary))
((book))
((telephone))
((computer))
((mouse))
etc

Thank

I recently learned about the wonderful new world of Array Formulas because of Macropod(You rock Btw!!)

Some new questions have arose from my learning of this wonderful function of excel.

Can Array formulas be added together ?

={=SUM(IF((DAY(FEB!D$9:D$133)A10),1))}+{=SUM(IF((DAY(MARCH!D$9:D$15)A10),1))}

I used the custom format "*"0"*"

to make each number have an * at the beginning and end, for barcoding purposes.
it only works with numbers
how can I make a custom number format that puts * at the beginning and end of any data entered?

eg-
ABCDE12345
would become
*ABCDE12345*

Thanks,
Corticus

I wish to create a complex (pricelist) template which contains many
named ranges. When creating new individual price lists which contain
common ranges from the template, I need to select say range called
"Header" which is several columns wide and several rows high.
I need to find a way to copy & paste the formatting and content of a
range, preserving different row heights and column widths, together
with titles etc.
So far I haven't found a solution. Any help gratefully appreciated.
Thanks in advance.
Kind regards
David

--
davidthegolfer
------------------------------------------------------------------------
davidthegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31806
View this thread: http://www.excelforum.com/showthread...hreadid=515286

How can I programmatically determine the Amount and MinusValues of a chart
series that has X and Y error bars? I need to change the error bars into
error ellipses but first I need the size of the individual error bars.
The ErrorBar method sets the Amount and MinusValues, but the ErrorBars
property appears to only allow access to what it looks like.

Hi guys,

im brand new to macros and im having some difficulty programming what i want done. I would greatly value any help or input someone may have for me.

heres what im trying to do:

1) I have 1 column with over 100+ rows of data and I am trying to automate the copy and pasting of each individual cell in this column, into another cell.

2)Once 1 cell is pasted in this cell - some data t is generated and I would like to copy paste these columns into a new spreedsheet.

I have written the following code for 1 cell which is working perfectly, but I am unsure how to loop it for the remaining 100+.

Sub OneCell()

Range("R10").Select
Selection.Copy
Range("B7").Select
Sheets("YahooOptionsQuotes").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select
Range("C9:C38").Select
Selection.Copy
Sheets("YahooOptionsList").Select
Range("C9:C38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select
Range("D9:D38").Select
Selection.Copy
Sheets("YahooOptionsList").Select
Range("F9:F38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select
Range("G9:G38").Select
Selection.Copy
Sheets("YahooOptionsList").Select
Range("G9:G38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select
Range("H9:H38").Select
Selection.Copy
Sheets("YahooOptionsList").Select
Range("D9:D38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select
Range("I9:I38").Select
Selection.Copy
Sheets("YahooOptionsList").Select
Range("E9:E38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select
Range("J9:J38").Select
Selection.Copy
Sheets("YahooOptionsList").Select
Range("H9:H38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("YahooOptionsQuotes").Select
Range("K9:K38").Select
Selection.Copy
Sheets("YahooOptionsList").Select
Range("I9:I38").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

I could just copy paste this code over and over just changing the rows/columns in the code but i believe this would slow down processing?
plus be extremely time consuming

can anyone advise me on what to do? thanks for the help

Hello,
Does anybody know how to display the Row and Column of an Active Cell and
display it in another cell.

Regards,

I am trying to lock the height and width of my Excel cells so that my overall
area won't change even if I change the size or amount of text in the box. Is
this possible?

Is there a way to determine the height and width of the Print_Area
range in a closed workbook using VBA.

Ultimately I want to determine the dimensions of the Print_Area so that
when I import it into a word document I maintain the aspect ratio of
the object.

Is there a way to set the Height and Width of an image that is copied to certain dimensions.

I would like my image to be set to a "Height" of 6.07 and "Width" of 8.90.

I now you can do this manually when you go in Format Picture and resize the image to these dimesions.

Can this be done with a code?

I wish to create a complex (pricelist) template which contains many named ranges. When creating new individual price lists which contain common ranges from the template, I need to select say range called "Header" which is several columns wide and several rows high.
I need to find a way to copy & paste the formatting and content of a range, preserving different row heights and column widths, together with titles etc.
So far I haven't found a solution. Any help gratefully appreciated.
Thanks in advance.
Kind regards
David

Is there anyway to highlight the row and column of the active cell without permanently removing either the conditional formatting in the highlighted row and column or the colour formats? I have the following code:

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 6
Columns(Target.Column).Interior.ColorIndex = 6
End Sub
But this gets rid of all conditional formatting in the active sheet, not ideal. Also, when I delete the code to stop using it the last row and column that was highlighted yellow remains yellow.

I know this might be a bit to ask but can anyone produce VBA code that will full fill my wish of being able to highlight the row and column of the active cell without ruining colour formatting?

Thanks in advance

I would like a macro that returns the width and height of an excel chart copied as a picture to the clipboard.

The reason I want to do this is that I want to paste pictures into textboxes in Word and ensure consistency of appearance. So, I’ll set the width of each textbox to a full text column width, adjust the pasted picture’s width to slightly less than the width of the box (maintaining aspect ratio so it doesn’t distort), then set the height of the textbox to a little bit more than the new height of the picture inside it.

So, in order to set the textbox’s size I’d like to know the dimensions of the picture to go inside it before it’s pasted.

Apologies if this looks more like a Word than Excel question

I have been searching for documentation which list each of the methods and properties of Excel in one place, preferrably on-line. Ideally the documentation would also include in the description of each strategies and examples of the expected and not expected use of each. My searches have returned hundreds of hits but nothing which was clearly the type of documentation I need. Any assistance will be greatly appreciated.

Hi guys.. I require help in writing macros for the following checks.. I am also attaching the sample workbook below..

1. Validation check_1: Check the length and value of “Customer#/ADU”.

The allowed conditions are listed below. If error conditions are raised show the Error message to the “Result” column line.

Column name Length Value Error Message
Customer# 6byte “No check” ERROR:Invalid Customer#
ADU 1byte 2/3/7 ERROR:Invalid ADU

I have used this snippet but it doesn't seem to work..
lastrow = Worksheets("Main data").Range("A" & Rows.Count).End(xlUp).Row 
mx = 6 
For i = 9 To lastrow 
    If Len("B" & i) < mx Then 
        Range("G" & i).Value = "ERROR:Invalid Customer#" 
        Enf If 
    Next i
2. Validation Check_2: Check the “Data Value”
Check the length of “Data Value” filed. Get the information of correct length of each value from “Length” field of “Table information”. “Blank” is also allowed for exceptionally condition..
If error condition is raised, show the Error message” ERROR: Invalid Data value” to the “Result line” field on “Main data” sheet.

Regards

Sravanthi

Hello!

Help urgently required!

I need to amend about 3000 cells on on worksheet to remove the first part of the formula and also the last part of the formula!

I cant just amend it and drag it down as its pulling from 20 different tabs!!!!!

Because I will be removing the (())'s Excel's automatic formula checking keeps kicking in on the normal find and replace (with nothing) and trying to auto-fix it for me.

Is there a way to build a basic macro to remove the desired text from the beginning and end of a formula without it being interrupted by the Excel auto-correction which we have been unable to disable?

Each cell has different contend in the middle as it is an oracle based application to load data which each cells loads with different dimensions which are being pulled from all over the sheet.

The formula is posted below and the part I need to remove are coloured in red!

=IF(G$5="CurFcstBudRate",IF('Fixed Assets'!G99=0,"",HsSetValue('Fixed Assets'!G99,$B$3,"Scenario#"&G$5&";Year#"&$B$5&";Period#"&G$6&";View#<Scenario View>;Entity#"&$B$2&";Value#<Entity Currency>;Account#"&$A100&";ICP#[ICP None];Custom1#"&$B100&";Custom2#[None];Custom3#[None];Custom4#BUManInput")),"")

Please help today if possible as we have a deadline pending!

Eternally Great-full!

Chris!

Hi,

I am using the following array formula to count the total of 2 different criteria on a separate sheet within the workbook. I have used this method before with success and don't understand why I am coming up with the result of zero this time.

=SUM(('Sheet 2'!F2:F39="A")*('Sheet 2'!F2:F39="B"))

I do hold control + shift + enter

Thanks,
Paula

I searched the web trying to find an answer to find a solution to my excel dilemma, but I could not find an exact solution. I found a few close answers, but not what I need.
I think this might require a macro to do what I am wanting.

I have 3 rows of data (1,2,3). Two rows contain basic entered numbers (1,2). The third row (3) contains a calculation based on the numbers in the same column in the other two rows (e.g. B4=B2+B3, C4=C2+C3, etc.)
I am then using an array formula based on all the numbers in row 2 and the calculated numbers in row 3.

My problem is that I have some blank cells in row 2 causing my array formula not to work for the data I have. I need an automated method of eliminating all the blank cells in row 2 along with the corresponding values in rows 1 and 3 above and below the blank cells. I would like for the columns containing non-blank values to shift left eliminating any colums with a blank value.

The second problem deals with the array formula. I am dealing with a large amount of data and I am not always sure how many or which cells in row 2 may be blank. I want my array formula to use all the nonblank cells in rows 2 and their corresponding values in row 3. This means my array formula will need to change to account for how ever many columns of nonblank data I have available. For example, my array formula may calculate for 10 columns of data, but 4 columns have blank cells causing incorrect results. Therefore, I need all the columns containing a balnk value to be eliminated from the array formula. I assume this could be done by automatically shifting all the columns containing full data to the left and automatically adjusting the array formula for the 6 columns of data rather than 10 columns.

I have attached a sample worksheet. I have simplified my exact formulas into this simplified scenario for better understanding, but the situation is the same.

I am having a major issue in getting an array formula to work for in counting multiple criteria. I am looking for the intersection of two types of data. One column (E2:E413) has data that has been coded 1,2,3, or 4. Another column (T2:T413) has data that has been coded 1,2,3,4,5,7, or 8. I am trying to count (not sum) how many times that both 1 shows up in column E AND 1 shows up in column T.
I have tried many different formulas, but I'm a novice at Excel and am need of help. The only familiarity I have with this program comes from the misery I've had with it in the last few days. Below are two of formulas that I have tried (found them via web), but to no avail:

=SUM(IF($E$2:$E$120="1",IF($T$2:$T$120="1",1,0),0))
=SUM(($E$2:$E$413="1")*($T$2:$T$413="1"))

If anyone can help, I would be so super appreciative.

Thanks,
Tiffany