Conditional Formula with Multiple Arguments

p_johnsen

New Member
I am running Excel 2003. I have a need to calculate the average number of days that my company's unpaid invoices are aging, and consider the actual days it took to receive payment. The objective is to calculate a single number - the average DSO (day sales outstanding) including both unpaid invoices and paid invoices. In a spreadsheet, I have Bid ID Numbers arrayed in Column A, Invoice Dates in Column B and Payment Received dates in Column C. Not all Bid ID Numbers will have a corresponding Invoice Date, because my company does not always win the bid. As a result, some rows have no Invoice Date in Column B (we lost the bid) and Payment Received date in Column C. Some rows have an Invoice Date in Column B (we won the bid) but no Payment Received date in Column C (we are waiting for payment). Some rows have dates in columns B and C (we have invoiced the client and received payment). I need to avoid the #VALUE! or #REF! error messages in the cells. I need help in creating a nested conditional formula that returns the number of days in Column D. If there is no date in Column B, then no value is returned in Column D. If there is an Invoice Date in Column B and no Payment Date in Column C, then the difference in days between Today() and the Invoice Date should show in Column D. If there is an Invoice Date in Column B and a Payment Date in Column C, then I need to return a value in Column D that represents the difference between the Invoice Date and the Payment Date. The single nested conditional formula in Column D needs to consider these three conditions outlined above. How do I do that??

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Marcelo Branco

MrExcel MVP
Maybe

D2
=IF(B2<>"",MIN(TODAY(),C2)-B2,"")

copy down

M.

Replies
1
Views
152
Replies
1
Views
134
Replies
1
Views
124
Replies
11
Views
509
Replies
22
Views
676

1,195,629
Messages
6,010,776
Members
441,569
Latest member
PeggyLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back