Posts by 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

Thanks Neale,

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

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

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

Hi, try this for the number of occurrences in the past 7 days;

With your dates in E:E

In F1 enter =E1-7 and drag down

In G1 enter =COUNTIF(E:E, ">=" & F1)-COUNTIF(E:E, ">" &E1) and drag down

HTH, 2rrs

Hi,

try this;
=SUMIF(L41:L49,">0")/COUNTIF(L41:L49,">0")

adjust range to suit.

2rrs

here is another free one called Pop-up Stopper.

http://www.panicware.com/support.html

2rrs

There are 2 format methods you can use without utilizing a formula:

format as time 37:30:55

or custom format [h]:mm:ss

2rrs

Try this

=TEXT(Q12,"0\:00")+0

2rrs

You can use custom format

Use d,hh, mm this will return days , hours, mins.

2rrs

go to "tools", options, view; check the formula box.
all formulas will appear and can be printed
is this what you want?

2rrs

Hi, here is a simple approach for finding duplicates:

1st sort your data on the col you want to check for dups

enter in C2 =IF(C2=C1,"Duplicate","")

then fill down.

2rrs

Hi tinka1,

I had the very same problem that I solved with the following:
I use E2 (header row) with new rows being added in E3. I use this for "Average", but it works for sum as well.

=SUM(OFFSET(E2,1,0):E112) "sum of all days" E112 is dynamic and will change to E113 etc as rows are added.

=SUM(OFFSET(E2,1,0):OFFSET(E2,5,0)) "sum of 5 days"

=SUM(OFFSET(E2,1,0):OFFSET(E2,10,0)) "sum of 10 days"

=SUM(OFFSET(E2,1,0):OFFSET(E2,15,0)) "sum of 15 days"

=SUM(OFFSET(E2,1,0):OFFSET(E2,25,0)) "sum of 25 days"

Hope this helps, 2rrs

Hi, here is a unsophisticated method;

In the date cell use 'ctrl ; enter', this will enter the current static date.

2rrs