[Solved] formulae that can be used to calculate YTD

Try this formula

=SUM(B7:OFFSET(B7,0,E1-1))

in cell N7

You can also use the CHOOSE function but i prefer the OFFSET.

Check ouit the Help for OFFSET its quite useful.

HTH

although note that offset is volatile, so usng it a lot can reduce the file performance. index() can often be used instead. eg, an index equivalent for Neale's

=SUM(B7:OFFSET(B7,0,E1-1))

would be:

=SUM(B7:INDEX(B7:M7,1,E1-1))

Thanks Paddyd, I just learned something (yet again).

Quote

Originally posted by Paddyd
although note that offset is volatile, so usng it a lot can reduce the file performance. index() can often be used instead. eg, an index equivalent for Neale's

=SUM(B7:OFFSET(B7,0,E1-1))

would be:

=SUM(B7:INDEX(B7:M7,1,E1-1))

Display More

Hi Paddy,

Great formula; but it is returning the sum (not the average); I changed the SUM to AVERAGE and it worked beautifully for ytd average.

2rrs

Quote

Originally posted by Imbuzi
Hi all,

I have yet another question....

I need to be able to calculate the year to date value of a range of numbers based on the based on a selection done by a user.

I tried to use a nested if statement but came unstuck when I got to if no 8.

I would appreciate any suggestions on this one.

TIA

G

Display More

Hi Imbuzi,

I see that Neale and Paddy have come up with a rather nice solution to your request.

I like the drop down that you have for the calendar; but could not figure out how it was done.

Could you or someone please describe how to go about setting that up.

tia, 2rrs

Hi 2rrs

try this

open a new workbook

enter Jul02 thru to jun03 in a range

right click toolbar select Forms

click combobox draw the box

ensure control tab is visible

select the range with the dates in the input range box

and select cell for the output range

click ok close forms toolbar

have fun
HTH

Quote

Originally posted by Neale
Hi 2rrs

try this

open a new workbook

enter Jul02 thru to jun03 in a range

right click toolbar select Forms

click combobox draw the box

ensure control tab is visible

select the range with the dates in the input range box

and select cell for the output range

click ok close forms toolbar

have fun
HTH

Display More

Thanks Neale,

I'll give it a try.
This is charting new water for me.

2rrs

Quote

Originally posted by Neale
Hi 2rrs

try this

open a new workbook

enter Jul02 thru to jun03 in a range

right click toolbar select Forms

click combobox draw the box

ensure control tab is visible

select the range with the dates in the input range box

and select cell for the output range

click ok close forms toolbar

have fun
HTH

Display More

Hi Neale,

It didn't take long for me to run into trouble!

I got as far as drawing the combo box; then found that my control tab was "gray", I tried to figure it out with no luck.

I did find this on Google MS excell public forum:

"Quote"
Ken Anderson wrote: > Hi, I am trying to create a drop down combo box in excel > 2000. I create the box and then right click over the top > of it and select 'Format Control'. At that point I'm > suppose to be able to go to the 'Control' tab and give it > the input range. However, the control tab isn't > there. 'Size, Protection, Properties and Web' tabs are > there, but no Control tab. If I go to a spreadsheet that > already has a drop down box created on it, and i right > click and goto the Format Control..it has a Control tab. > But there isn't one when I try to create it from scratch.

"Response"
The existing combo boxes are from the Forms toolbar, and you're using the combo box from the Control Toolbox. To set the properties for your combo box, select the combo box, and click the Properties button on the control toolbox. You can set the ListFillRange (like Input range), LinkedCell, and ListRows properties.
-- Debra Dalgleish Excel FAQ, Tips & Book List
___________________________________

I attempted to follow the directions, but I am lost.

Help please, 2rrs

Hi 2rrs

Are your using the Forms Combo box or the Control Toolbox Combo box???

If your are using the Forms one then Right click it and select Format Control ( i may have missed that step) and then select the Control tab

I don't use the ones on the control toolbox very much.

HTH

This example uses a combination of OFFSET,NOW and MONTH functions to sum only YTD values, ignoring projected values for the remainder of the year.

=SUM(OFFSET(B2,0,0,1,MONTH(NOW())))

Roy