Question about Microsoft Excel for PC

I have column A showing months 1 to 12, and column B is 12 values. In column C I have the formula "{=TREND(C3:C14,A3:A14)}". I have use F2 and CTRL+SHIFT+ENTER to create the array but only get a result in one cell. I have even used the example in Excel "Help" but has not helped.

Cheers

Let's say you have a criterion in column A that must be true; any true record should be included in the trend estimate for your unknown X value "5", with X's in column B and Y's in column A, as follows:

A B C

TRUE 1 2

TRUE 2 4

FALSE 3 0

TRUE 4 8

The formula is

=TREND(IF(A1:A4,C1:C4,AVERAGE(C1:C4)),IF(A1:A4,B1:B4,AVERAGE(B1:B4)),5)

This is the only way to do it, because TREND doesn't like falses in the array, and you can't use any other number except for the average, else it will mess with the trend results. Try it for yourself - it works!

Posted on Aug 05, 2009

Hi Griffnz,

Your "known Y's" or 'values' are in Column B. This is the first array in the Trend formula.

Your "known X's" or 'months' are in Column A. This is the second array in the trend formula.

The trend formula is supposed to give you a projection of what the rest of the values in Column B will be over the next few months (usually continuing cells in Column A). The cells you want these values to show up in represent the third array in the formula.

Thus, your formula should look more like: '=trend(B3:B14,A3:A14,A15:A18)'

However, your formula is leaving out The values in B and adding values from C - -- but there ARE no values in C. Apparently, C is where you want the values to appear. In that case, the C array would be the third array in your formula. This would look more like '=trend(B3:B14,A3:A14,C3:C14)

If this doesn't make sense, let me know.

Posted on Oct 01, 2008

Hi,

a 6ya expert can help you resolve that issue over the phone in a minute or two.

best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

goodluck!

Posted on Jan 02, 2017

I assume that the dates you want adjust are in date form and are in regular columns (or rows).

If so, then you can use a formula like this =DATE(YEAR(A3)+1,MONTH(A3),DAY(A3))

where the original date is in cell A3.

You can then copy this formula to calculate all the new dates you need, and then copy the results back over the original dates as VALUES.

If so, then you can use a formula like this =DATE(YEAR(A3)+1,MONTH(A3),DAY(A3))

where the original date is in cell A3.

You can then copy this formula to calculate all the new dates you need, and then copy the results back over the original dates as VALUES.

Oct 17, 2013 | 1996 Italdesign Formula

Yes,

That is Select the Cells which you want to SUM and Press**Alt+=**

This will bring the total just below the selected cells.

Thanks

Iqbal

- Select the column. (click on top of the column it will select)
- Right Click and select Format Cells.
- Select
**Currency**in**Category**then press**OK**

That is Select the Cells which you want to SUM and Press

This will bring the total just below the selected cells.

Thanks

Iqbal

Apr 02, 2008 | Computers & Internet

Make sure, when you entr a formula that you hit the ENTER key instead of just moving to tyour next cell. To have all numbers added or moved, use the"Special" application, or change the value. Such as -if you are working in text mode and you enter numbers, they are only numbers in text mode. They don't have a value. Correct the cells to reflect numbers. Right click, format cell - select what you want.

Nov 05, 2009 | Microsoft Office Excel 2003 for PC

=VLOOKUP(A2;Sheet1.$A$3:D27;2;0)

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

Feb 11, 2009 | Microsoft Excel for PC

Go to the cell you want this total in.

Type this formula:

=SUM(IF(Sheet2!C1:C10="EME",IF(Sheet2!N1:N10=1,1,0)))

make sure you end the formula with CTRL - SHIFT - ENTER which makes it an array formula. If you forget, go back to the cell with this formula and press F2 (to edit the cell) and press CTRL - SHIFT - ENTER to convert it to an array formula (Excel will show a little {...} around the formula).

Type this formula:

=SUM(IF(Sheet2!C1:C10="EME",IF(Sheet2!N1:N10=1,1,0)))

make sure you end the formula with CTRL - SHIFT - ENTER which makes it an array formula. If you forget, go back to the cell with this formula and press F2 (to edit the cell) and press CTRL - SHIFT - ENTER to convert it to an array formula (Excel will show a little {...} around the formula).

Dec 21, 2008 | Microsoft Excel for PC

=10000*(1+0.96)^12

=10000*(1+0.10)^18

=10000*(1+0.10)^24

=10000*(1+0.10)^18

=10000*(1+0.10)^24

Dec 02, 2008 | Microsoft Office Professional 2007 Full...

Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Jul 08, 2008 | Microsoft Computers & Internet

The solution I've used in similar situations is to create a 3rd column C with the items in column A and column B concatenated.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

May 27, 2008 | Microsoft Excel for PC

The problem doesn't say this explicitly, but it looks like you're using Excel. This answer depends on that assumption being correct.

Are you saying that some of your inputs are blank? And LOOKUP fails to map them to 0? Or are you saying that some of your values come back from LOOKUP as blanks? (They shouldn't; lookup should return either a value from the lookup table or an error.) Or is it the case that some input values get mapped to blank by your LOOKUP function?

Assuming that you're getting blanks back from LOOKUP where you wanted zeros -- for whatever reason -- here is something you can try.

It looks like the value you're looking up is in cell A3. Let's say your formula (the result of the LOOKUP) is in B3. In C3, you can put the formula =IF(ISBLANK(A3), 0, A3). This function tests if A3 is blank. If it is, it returns a 0. If it's not, you get A3 again. (But A3 has to really be blank -- spaces will count as non-blank.)

Then, you can copy data out of column C instead of column B, and it should have zeros in place of blanks.

Are you saying that some of your inputs are blank? And LOOKUP fails to map them to 0? Or are you saying that some of your values come back from LOOKUP as blanks? (They shouldn't; lookup should return either a value from the lookup table or an error.) Or is it the case that some input values get mapped to blank by your LOOKUP function?

Assuming that you're getting blanks back from LOOKUP where you wanted zeros -- for whatever reason -- here is something you can try.

It looks like the value you're looking up is in cell A3. Let's say your formula (the result of the LOOKUP) is in B3. In C3, you can put the formula =IF(ISBLANK(A3), 0, A3). This function tests if A3 is blank. If it is, it returns a 0. If it's not, you get A3 again. (But A3 has to really be blank -- spaces will count as non-blank.)

Then, you can copy data out of column C instead of column B, and it should have zeros in place of blanks.

Jan 05, 2008 | Computers & Internet

Suppose the value for $ is stored in cell A3. Your formula would look like this: =(A3+A3*0.25)*1.5

The equals sign at the beginning of the formula is necessary. And if you want the result to be formatted as currency, you can do so by right-clicking the cell or column, format cell, number tab, choose currency.

The equals sign at the beginning of the formula is necessary. And if you want the result to be formatted as currency, you can do so by right-clicking the cell or column, format cell, number tab, choose currency.

Nov 15, 2007 | Computers & Internet

Jan 28, 2016 | Microsoft Excel for PC

656 people viewed this question

Usually answered in minutes!

In the mentioned example the formula reads:

"{=TREND(B3:B14,A3:A14)}".

Cheers

Excel shows that trend of current data is possible (see excel TREND example). However, this is not consistent. In D3 I typed "=TREND(C3:C6, B3:B6)" followed by F2, CTRL+SHIFT+ENTER and got:

ColB ColC ColD

1

$133,890

133842

2

$135,000

134944

3

$135,790

136046

4

$137,300

137148

When I typed the same thing again in the same cell I got

1

$133,890

133842

2

$135,000

3

$135,790

4

$137,300

Very strange. However, TRENDLINE solved the problem but it would be nice to know why the formula didn't.

×