Sumif for Date Range
Microsoft Excel's
Sum function allows you to sum the values
in a column. E.g., if I have a column, column J, that contains revenue in
dollars for items sold with the first entry in row 4 and the last entry in
row 62, I could use the formula
=SUM(J4:J63) to calculate the
total revenue for all items (I use "63" instead of "62" presuming that new
entries will continue to be added by inserting new rows above row 63). But,
if you wanted to determine the revenue just for the current month, presuming
the month is April 2012, you could use the
sumif function, e.g.,
assuming the date of sale for each item is in column F, I could use the
formula
=SUMIF(F4:F63,">=2012-04-01",J4:J63) to calculate the
revenue for any items sold on or after April 1, 2012, presuming that I am
still in the month of April 2012. In this case any row that has a date
value of April 1, 2012 or later in column F will have the sales amount in
column J for that entry added to the total sales calculation for April.
I could also have used "01-Apr-12" for the date value in the formula, if
I wished, instead of "2012-04-01", i.e., I could use YYYY-MM-DD where YYYY
is the year, MM is the two digit value for the month and DD is the two digit
value for the day or DD-Mon-YY, where Mon is the 3 letter abbreviation for
the month.
Presuming that the only entries on the worksheet are for the year 2012,
I could find the total sales for January using the formula
=SUMIF(F4:F63,"<=31-Jan-12",J4:J63). But, if I want to
determine the sales for February, I need to add only entries where the
date is between February 1, 2012 and February 29, 2012. One simple way to
do so that will work even for older versions of Excel, such as
Microsoft® Excel 2000, is to perform one SUMIF calculation
for all the values greater than or equal the starting date and then subtract
from that total the value calculated using another SUMIF formula
that adds all the values where the date is greater than the ending date. E.g.,
if I wanted to determine the total revenue for February, I could use the forumla
below:
=SUMIF(F4:F63,">=2012-02-01",J4:J63)-SUMIF(F4:F63,">2012-02-29",J4:J63)
The first SUMIF adds the sales amount in column J whenever the
date value in column F is greater than or equal February 1, 2012. The next
SUMIF adds the sales amount in column J whenever the date
value stored in column F is greater than the last day in Feburary of 2012,
which is February 29, 2012. The result of the first SUMIF will
be the total for all entries on or after February 1, 2012. The result of
the second SUMIF will be the total for all entries after February
29, 2012. The difference between those two values will be the total for all
sales in the month of February 2012.
[/os/windows/office/excel]
permanent link
Mac OS X Hibernate Mode
Mac
OS X provides a
hibernation
feature, which allows you to save the current state of the system to
memory or the hard drive. You might wish to use this feature if you
are going to be away from the system for awhile and don't want to close
all of the applications you have open or you can't power the system from
a wall electrical outlet and wish to conserve battery power.
OS X supports five different hibernate modes. The default mode is "3",
which means when you put the system to "sleep" it saves the current state
of the system to Random Access Memory (RAM), blanks the display, and goes
to a low power consumption state.
-
0 - Old style sleep mode, with RAM powered on while sleeping, safe sleep
disabled, and super-fast wake.
-
1 - Hibernation mode, with RAM contents written to disk, system
totally shut down while “sleeping,” and slower wake up, due to
reading the contents of RAM off the hard drive.
-
3 - The default mode on machines introduced since about fall 2005.
RAM is powered on while sleeping, but RAM contents are also written
to disk before sleeping. In the event of total power loss, the
system enters hibernation mode automatically.
-
5 - This is the same as mode 1, but it‘s for those using secure virtual
memory (in System Preferences –> Security).
-
7 - This is the same as mode 3, but it‘s for those using secure virtual
memory.
You can determine the current hibernate mode by opening a Terminal window
(use the Finder to go to Applications then Utilities
then double-click on Terminal) using
pmset -g | grep hibernatemode.
$ pmset -g | grep hibernatemode
hibernatemode 3
You can change the hibernation mode by using sudo pmset -a
hibernatemode n where n is one of the above integers
representing a supported hibernation mode. E.g., to write the current state
of the system, which includes the open applications and files in memory,
to the hard drive and completely shut down the system, you can set the
hibernatioon mode to 1. This is akin to hibernate mode on a
Microsoft Windows system.
$ sudo pmset -a hibernatemode 1
Password:
$ pmset -g | grep hibernatemode
hibernatemode 1
To put the system in hibernation, you can click on the Apple icon in the
upper, left-hand corner of the screen and select Sleep. The system
will then be put in the selected hibernate mode.
References:
-
Does Mac OS X support hibernation?
superuser
[/os/os-x]
permanent link
Copying a Disc with Burn under Mac OS X
The
Disk Utility application that comes with Mac
OS X, at least as of
OS X
10.6 ,aka
Snow Leopard, does not permit
one to copy an audio CD. An alternative application that can be used to
create an
.iso file from an
audio CD is
Burn,
which is free software licensed under the
GNU General Public License (GPL)
.
[ More Info ]
[/os/os-x/software/utilities/cd-dvd]
permanent link
Show the list of installed packages on a Ubuntu system
To display the list of installed packages on a
Ubuntu Linux system from a shell prompt,
you can use
dpkg --get-selections. You can also use the
Ubuntu Software Center, if you prefer a
GUI method of checking.
$ dpkg --get-selections | more
accountsservice install
acl install
acpi-support install
acpid install
adduser install
adium-theme-ubuntu install
aisleriot install
alsa-base install
alsa-utils install
amazonmp3 install
anacron install
If you only want to determine whether a paricular package is installed,
you can use grep to filter the results.
$ dpkg --get-selections | grep libboost
libboost-filesystem1.46.1 install
libboost-serialization1.46.1 install
libboost-system1.46.1 install
You can find the locations for the files installed when the package was
installed by using the -L option with dpkg
$ dpkg -L libboost-filesystem1.46.1
/.
/usr
/usr/lib
/usr/lib/libboost_filesystem.so.1.46.1
/usr/share
/usr/share/doc
/usr/share/doc/libboost-filesystem1.46.1
/usr/share/doc/libboost-filesystem1.46.1/copyright
/usr/share/lintian
/usr/share/lintian/overrides
/usr/share/lintian/overrides/libboost-filesystem1.46.1
/usr/share/doc/libboost-filesystem1.46.1/NEWS.Debian.gz
/usr/share/doc/libboost-filesystem1.46.1/README.Debian.gz
/usr/share/doc/libboost-filesystem1.46.1/changelog.Debian.gz
To obtain information regarding a package, including its description,
you can use the -s or --status option followed
by the complete package name.
$ dpkg -s libboost-filesystem1.46.1
Package: libboost-filesystem1.46.1
Status: install ok installed
Priority: optional
Section: libs
Installed-Size: 208
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Architecture: i386
Source: boost1.46
Version: 1.46.1-5ubuntu2
Depends: libboost-system1.46.1 (>= 1.46.1-1), libc6 (>= 2.4), libgcc1 (>= 1:4.1.1), libstdc++6 (>= 4.1.1)
Description: filesystem operations (portable paths, iteration over directories, etc) in C++
This package forms part of the Boost C++ Libraries collection.
.
The Boost Filesystem Library provides portable facilities to query and
manipulate paths, files, and directories. The goal is to
facilitate portable script-like operations from within C++ programs.
Homepage: http://boost.org/libs/filesystem/
Original-Maintainer: Debian Boost Team <pkg-boost-devel@lists.alioth.debian.org>
[/os/unix/linux/ubuntu]
permanent link
Memory for Asus P4P800-E
I installed two additional memory modules in an ASUS
P4P800-E motherboard. The motherboard has 4 memory slots that
take
DDR1 memory
modules. The system had two 1 GB memory modules already installed.
I installed two additional 1 GB memory modules giving the system 4 GB
of
RAM, which is the maximum
amount of memory the P4P800-E motherboard will support. I used
Corsair VS1GB333 memory
modules. After I installed the additional memory, I used the free
MemScope Memory
Tester software to test all of the memory in the system. I let the
test run over night for six complete passes and part of a seventh pass with
no errors found.
Note: you can download the MemScope Memory Tester software
here. At the
moment there is a missing image on the page that has a link associated with
it for downloading the software. The link appears immediately below the
"Test 7 Random Data Sequence Test" paragraph or you can click
here to access the form you need to fill out to download the software.
[/pc/hardware/motherboard]
permanent link
Recovering Lost Photos with DEFT Linux and PhotoRec
DEFT Linux can be used for
forensics examination of a disk drive or to recover deleted or otherwise
lost files from a disk drive.
If you have lost files on a device, such as a memory card from a camera,
perhaps because you accidentally deleted them, you can use DEFT and
the
PhotoRec utility that comes with
DEFT 7 to search for and recover
those files.
PhotoRec 6.13 comes with
DEFT 7. PhotoRec was created by Christophe Grenier. It is also
available for
Mac OS X and Microsoft Windows systems.
[ More Info ]
[/os/unix/linux/deft]
permanent link
