Question about Microsoft Excel for PC

3 Answers

Excel formula help

When I have a pivot table it has certain numbers I want to grab. If I link to a cell within the pivot table it uses text to search for the data in the pivot table. A2 =GETPIVOTDATA("P/S",Sales!$A$3,"Sales Categories","Range < 50")
Rather than use "Range < 50" I want to grab text from another cell (say B2= "Range < 40") to make the model more dynamic. However the get formula searches by text and not the cell link. What I want is something like this A2 =GETPIVOTDATA("P/S",Sales!$A$3,"Sales Categories",&B2) but unfortunately this does not work.
Do you know how to get around this issue?
Thank you.

Posted by on

3 Answers

  • Level 1:

    An expert who has achieved level 1.

  • Contributor
  • 1 Answer

Formula for the keys or short curt for excel

Posted on Jul 28, 2008

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Vice President:

    An expert whose answer got voted for 100 times.

  • Expert
  • 407 Answers

This problem arose from Excel Verson 2003 onwards - It does not appear in a sum function where the range is greater than one cell.

The easiest solution is to manually linking the cell in your formula and typing in the cell reference rather than looking it up

Posted on May 25, 2009

Sum
person tege
max

Posted on Jul 22, 2008

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

2 Answers

What are the formulas in grading the grades uisng microsoft excel


Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.

Formulas used in Excel must follow a certain syntax.

  1. All formulas begin with an equals sign (=).
  2. Some formulas use operands such as +,-, *,/ for addition, subtraction, multiplication or division.
    For example, the formula =A1+A2+A3 would add the contents of cells A1, A2 and A3.
  3. Other formulas refer to different functions such as SUM, AVERAGE and others.
    For example, the formula =SUM(A1:A3) would add the contents for the range A1 through A3.
  4. Formulas can be combined with operands.
    For example, the formula =10*SUM(A1:A3) would add the contents cells A1 through A3 and multiply them by 10.
  5. Functions can be nested within each other.
    For example, the formula =SQRT(10*SUM(A1:A3)) would take the square root of ten times the sum of cells A1 through A3. When functions are nested, it is important that the number of left parentheses match the number of right parentheses.

Aug 19, 2011 | Microsoft EXCEL 2004 for Mac

Tip

HOW TO COUNT THE OCCURRENCES OF A TEXT STRING


In the cell that you want the result to appear in, enter the appropriate formula from the following examples.
How to Count the Occurrences of a Number
Use this formula
=SUM(IF(range=number,1,0))
where range is the range that you want to search, and number is the number that you want to count.
NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.
How to Count the Occurrences of a Text String
Method 1
Use this formula
=SUM(IF(range="text",1,0))
where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).
NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.
Method 2
Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula
=COUNTIF(range,"text")
where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).
NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.
Wildcard characters can be used within the COUNTIF function.
The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:
=COUNTIF(a1:a10,"*x*")
The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."
=COUNTIF(a1:a10,"?x*")

on Nov 11, 2013 | Microsoft Excel Business & Productivity...

1 Answer

Sum of the greatest run of negitive numbers


Hi, Not sure if this is what you have tried:
1: Assuming each run is on a different column
Run1 Run2 Run3 Run4, etc...and the maxium lenght of each column is 9 values, but each colum has a different lenght of numbers..
Then you can use:
=MAX(COUNTIF(A2:A10,"<>0"),COUNTIF(B2:B10,"<>0"), {keep adding countif for each colum).

If all runs are on the same colum, then using Pivot tables is easier. If you have a copy of your data, please post it so that I can give you the exact formula ;)

Oct 21, 2009 | Microsoft Excel for PC

1 Answer

IF Formula


You can use this formula
=IF(A2<=100,"Within budget","Over budget")
Which means
If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget)
or you and try something like this
=IF(A2=100,SUM(B5:B15),"")
which means
If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()
I got these examples from the help within Exel they give several more examples and more expaination.

Jan 10, 2009 | Microsoft Office Standard for PC

2 Answers

Min and Max Date range filter


Check out: Tools -> Data -> Filter -> Auto Filter.
You may want to look at the help function to get a better idea of how powerful this feature is.

Let me know if this helps.

May 08, 2008 | Microsoft Excel for PC

1 Answer

Data for excel chart


You need to create subtotals so that it looks like something like this:

Prod/units Amt. Level Total

xxxx 10.00 1
yyyy 20.00 1
30.00

dddd 40.00 2
ffff 30.00 2
70.00

etc.

To do this I would suggest using the Pivot Table feature of Excel. It is not too hard to use but you do have to know what you want and also have to be sure you are getting results as expected. Like any tool, it has to be used right. You can also use sub-total functions supplied in VBA as well in your cells.

If you want to avoid using VBA functions in cell and deal with ranges, etc., use the Pivot Table Wizard.

Some reading:
http://www.ehow.com/how_13020_pivot-table-wizard.html
http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Also Google "Excel pivot table wizard" to get more.

Good luck!

Feb 23, 2008 | Microsoft Office Standard for PC

1 Answer

Lookup,s


If you can move your name column (C) to the first column, you could leverage the VLOOKUP formula pretty easily.
To do this, do the following:
1) Move the C Column to be the A Column, shifting all other columns to the right.
2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)
3) Use A1 as your search field.
4) In A2, enter the following formula:
=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:
$A$1 -> the search field (name your looking for).
$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.
3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.
Also, if you can sort column A (names) it would find results faster, if your data set is large.

Feb 03, 2008 | Microsoft Excel for PC

1 Answer

Excel Formula


Make sure that the cell is formatted as a number, not as text. (Right-click the cell, format cell, number, choose NUMBER.)

Dec 19, 2007 | Microsoft Excel for PC

2 Answers

FORMULA FOR TEXT FORMATING


Yes of course, if you have a little background of visual basic programming that's possible you can enhance your requirements.

Please see link http://www.techonthenet.com/excel/questions/cond_format1.php

Nov 29, 2007 | Microsoft Excel for PC

Not finding what you are looking for?
Microsoft Excel for PC Logo

154 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...