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.)

## Related Results

### How does AND work in array formulas?

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.

### Array Formula: I need a formula to count disctints like-

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

### Highlighting a cell to the left and right of ever cell that is currently highlighted?

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
Formula1:="=\$D\$6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
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

### Check the length and value of specific columns and comparion check between two sheets

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

### How to add a a word or number at the front and end of the column

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.

### Add something at the beginning and end of wordings

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

### Can Array formulas be added?

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))}

### Inserting * at the beginning and end of string

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

### Copy the formatting and content of a range

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.
Kind regards
David

--
davidthegolfer
------------------------------------------------------------------------
davidthegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31806

### How to read the Amount and MinusValues of ErrorBars in a series

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

### Loops - automate the copy and pasting of each individual cell in this column

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

### How to locate the Row and Column of an Active Cell

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

Regards,

### Is it possible to lock the height and width of a cell?

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?

### Determining the height and width of Print_Area in a closed workbook

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.

### Setting the Height and Width of an Image

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?

### Copy the formatting and content of a range

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.
Kind regards
David

### Highlight the row and column of the active cell

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?

### Macro to return the height and width of an object in the clipboard

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

### Where to find documentation which list each of the methods and properties of Excel

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.

### Check the length and value of specific columns and comparion check between two sheets

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#

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

### Urgent Help Required! Double string macro to remove the 1st & last part of Forumla

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")),"")

Eternally Great-full!

Chris!

### Count 2 criteria using Array formula

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

### Shifting Nonblank Cells and Adjusting an Array Formula for the Nonblank Cells

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.

### Multi-criteria Counting (array formulas)

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