Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - 3D formulas
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel - 3D formulas
Resolved · Low Priority · Version 2007
Reply Fri 28 Aug 2009, 16:48Delegate Sally said...
Sally has attended:
Excel Introduction course
Excel Intermediate course
Powerpoint Intermediate Advanced course
Excel - 3D formulas
I cant understand the instructions in the intermediate manual
In Sheet one called IF Function I have a list of commission amounts (column G)
In Sheet two called Conditional, I have a list of totals (column F)
I want to take the commission amounts from sheet one and add them to the totals in sheet two creating new figures in column G - ie totals with commission added.
THe manual tells me to select the cell where the formula is to be entered: this is col G in sheet two. It then tells me to click on sheet tab of the first worksheet to be entered into the formula (sheet two)). I am then told to hold shift key and click on the sheet tab for the last worksheet to be entered into the formula - sheet one. At this point, the new formula in sheet two, col g reads: =SUM('IF function:Conditional'!
I am then told to highlight the cell on the sheet I have just selected - sheet one. I then go back to sheet two and the formula just says =SUM(Conditional!G6. The IF function name has disappeared. G6 is the cell ref in sheet one but G6 in sheet two is where the formula is. I then completed the formula to read =SUM(Conditional!G6+F6) but it didnt work as the IF function (sheet one) no longer appeared in the formula.
I assume the formular should read: =Sum('IF Function:Conditional'!G6+F6). If I enter manually, an error message of REF comes up in the cell.
For upcoming training course dates see: Pricing & availability
RE: Excel - 3D formulas
Hello Sally
Thank you for your question.
Can you please let me know which page the exercise is on that you are referring to? I gather that this is for a practice activity in the handbook you are given on the course?
Kind regards
Amanda
Reply Thu 3 Sep 2009, 10:59Delegate Sally said...
RE: Excel - 3D formulas
H iAmanda
The exercise for 3D formulas is on page 14of themanual we used on the training day
RE: Excel - 3D formulas
Hi Sally
A 3D formula is used to refer to values that are in the same cells in multiple sheets.
In this case because the totals in one sheet are in a different column to the commission amounts in the second sheet, a 3D formula wouldn't work to add the amounts together, since the totals and the commission amounts are not in the same cells on each sheet.
Also when you are putting together a formula which refers to cells on different sheets to where the formula is in the workbook, always use Enter to finish inputting the formula, otherwise you tend to run into problems.
You could add the two amounts together by:
1. Entering the Sum function where you want the formula result to go =sum()
2. Position your cursor inside the brackets ()
3. Go to the first sheet and select the first cell to be included in the addition.
4. Type in a comma after selecting the cell
5. Go to the second sheet and select the next cell you'd like to include in the addition.
6. Press Enter to complete the formula and return to the sheet where you started.
Kind regards
Amanda
Thu 10 Sep 2009: Automatically marked as resolved.
|