Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.
Tag Archives: mixed references
Relative and Fixed Reference Trick in Excel
Find and Replace is your friend
When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick.
Reduce Excel Formula Length
Remove redundant references
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
Totalling Tip For Excel
Make the most of SUMIF
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
Sequential Numbers With a Twist
How to ignore hidden rows
You want a sequential number in a column. The challenge is, it must display as sequential even if rows are hidden or filtered. Is this possible?
Handling Relative References in Excel [VIDEO]
When you copy a formula in Excel, any relative references (those without dollar signs) may change depending on where you paste the formula. If you would like to copy a formula and not have the relative references change you have two options.
Excel Formula Dialog Frustration Solved
Make editing easy
In many Excel dialogs when you use the arrow keys to move around the formula you actually insert cell references from the active cell in the sheet below.
This is frustrating as you usually only want to move within the formula and you might have to exit out of the dialog and start again.
The Repeat key and $ signs
Do more with F4
The F4 function key in Excel has a dual personality.
When used normally it will repeat your last action. This is useful for formatting, deleting rows and other operations that require a few keystrokes. Pressing F4 can reduce the keystrokes. F4 can also repeat running a macro.