Formula question

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
I have seen a few scenarios where you can Highlight some cells, enter an formula in the active cell and then voila it will give you the right result when you press enter however on other occasions i have seen where you have to enter the formula and then drag and copy it down

Im not sure if they were Array formulas but im pretty sure that it gave a different result. I would have thought it would have given the same result?

Just wanted more info on when and where i would see this kind of scenarios

Thank You
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
I think you want to highlight a range of cells, enter something in one of them, and then hit ctrl+enter to write the same value / formula into all at once

An array formula is written by selecting shift+ctrl+enter. Chip Pearson gives a useful overview of the different types, and some examples of using them http://www.cpearson.com/Excel/ArrayFormulas.aspx
 

macness

New Member
Joined
Jul 27, 2010
Messages
37
you can Highlight some cells, enter an formula in the active cell and then voila it will give you the right result when you press enter
This person is probably in the middle of a formula that requires a range (more than one cell). for example =sum(a1:a10) can be entered manually or typing "=sum(" and then highlighting the cells, then pressing enter.

i have seen where you have to enter the formula and then drag and copy it down
You are referring to the fill handle - they are not necessary array formulas (probably arent in most cases) but you can write a formula to be static or dynamic depending on the need.

For example if I had a range that had total sales for sales people and i had a cell that had the commission percentage, i could write a formula that would calculate the commission a particular salesperson gets. I could then copy the formula down to apply that to all salespeople (without changing the percentage but changing the sales amount).
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,807
Messages
5,513,516
Members
408,956
Latest member
DianChris

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top