You are here:

Excel/Excel Formula questions

Advertisement


Question
Here is the scenario:

In my excel Spreadsheet:

Column A = the clients name
Column C = The date that they became a client
Column D = Status (whether they are active or dormant
Column E = The Lifetime amount of what we have billed them since they became a client

I need a few formulas:

1.  I need a formula that will give me the $ amount of the highest invoiced "active" client.

2.  I need a formula that will give me the name of the highest invoiced "active" client.

3.  I need a formula that will give me the name and the $ amount of the highest amount invoiced "active" client per time they have started with us.

For example I have a client1 that started in 2001 and their lifetime amount is 1.2 invoiced and they have been a client for 142 months.  So on average per month we have invoiced them $8,450.  Another client2 has been with us 48 months and we have invoiced them 800K total which means we invoice them $16,666 per month on average.  In Question# 3 above I would want to see Client2 in that cell.  

Are there formulas that can do this.  I was trying to use the MAX, MAXIF but had no success

Answer
Please use the web address in my signature below to send me a sample file with a smaller set of data, 5-10 rows of data, and a mockup of the desired results from that mockdata.

================

Based on the file you sent, the formulas could be:

G4:  "Highest Invoiced Active"
G5:  "Highest Invoiced Average"

These next two formalas are array formulas, confirm them by pressing CTRL-SHIFT-ENTER to activate the array each cell, you will see curly braces {} appear around your formulas.

H4: =MAX(IF(D4:D11="Active", $E$4:$E$11))
H5: =INDEX($E$4:$E$11, MATCH(MAX(ROUND($E$4:$E$11/(DATEDIF($C$4:$C$11, TODAY(),"m") + 1), 2)), ROUND($E$4:$E$11/(DATEDIF($C$4:$C$11,TODAY(),"m")+1), 2), 0))


Regular formulas:

I4:  =INDEX($A$4:$A$11,  MATCH(H4, $E$4:$E$11, 0))
I5:  =INDEX($A$4:$A$11,  MATCH(H5, $E$4:$E$11, 0))
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.