Populate the higher of two values based on date.

beadgirlsa

New Member
Joined
Feb 16, 2011
Messages
14
I would am trying to make a total of 2011 sales in Column A.

A(Invoice2011) B(Renewal) C(New Net) D (Desired)
1/15/2011 0 $500 $500
2/23/2011 $420 $0 $420
5/20/2011 $100 $0 $0

If the Invoice2011 date is >=TODAY() in the invoice column, I want Column D to show the higher of the two columns (or, the column that contains data, because only B -or- C should contain data, but not both)

This way, I can get an up-to-date total of the sales in 2011.

I've written several formulas (which I could paste here but none of them has been right) so asking for assistance from someone who may be able to help.

Thank you in advance for any expertise you may be able to offer.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

AlisaA

Board Regular
Joined
Mar 17, 2010
Messages
193
I'm a little confused as to what you are trying to do here. You state that you are looking to to get an up-to-date total of 2011 sales, but you want column D to only show data when the invoice date is greater than or equal to TODAY(). I'm not sure I understand how that is going to give you 2011 to date, as it is going to exclude more 2011 data every day...???
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello,

As per your original post, the desired result will give you only if Invoice Date <=TODAY().

Enter in a non usable cell, eg: H1 = TODAY(), then D2 copy down...

=MAX(B2:C2)*(A2<=$H$1)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,487
Messages
5,529,156
Members
409,851
Latest member
Ingar
Top