Question description:
This user has given permission to use the problem statement for this
blog.
I need Column D to = Column C - Column A only if Column B has a 'Date' entered.
Solved by I. B. in 11 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/09/2018 - 04:00
Hello, I understand that you want column D value to be A-C but only if column B has a date, right?
User
05/09/2018 - 04:01
Yes please, i will just write an example
Excelchat Expert
05/09/2018 - 04:01
Yes, an example would help.
User
05/09/2018 - 04:01
Like this
Excelchat Expert
05/09/2018 - 04:02
Okay, so instead of B, we are looking at C for a date?
User
05/09/2018 - 04:02
Yes that's right
Excelchat Expert
05/09/2018 - 04:02
One problem with your data is that the value in column B is not a valid number.
Excelchat Expert
05/09/2018 - 04:02
The £ in there forces the value to be a text instead so we can't calculate.
Excelchat Expert
05/09/2018 - 04:03
The value in column B should just be 100 and then formatted to display the currency.
Excelchat Expert
05/09/2018 - 04:03
Otherwise, the formula will be unnecessarily complicated.
User
05/09/2018 - 04:03
Oh ok I can leave it as a number then, that won't be an issue
Excelchat Expert
05/09/2018 - 04:03
Thank you. Next, what's the expected value if there's a date in the Paid Date.
User
05/09/2018 - 04:04
That would then be 0, and without a Paid Date I'd like it to be 100
Excelchat Expert
05/09/2018 - 04:05
In other words, if there's a date, the Outstanding will always be 0?
Excelchat Expert
05/09/2018 - 04:05
So no real need to subtract Fee from Total, just 0 would be fine? That's because your Total is linked directly to your Fee so it will always be 0, right?
User
05/09/2018 - 04:05
In this example yes, but the spreadsheet I'm developing will have multiple fees per row, I'll just show this quickly
Excelchat Expert
05/09/2018 - 04:06
Ah, since you linked D to B, that threw me off.
Excelchat Expert
05/09/2018 - 04:06
If there's not a date in column C, what would the value of Outstanding be?
User
05/09/2018 - 04:07
So if C had a date but E did not it would show 200
User
05/09/2018 - 04:07
If they both had dates it would then be 0
User
05/09/2018 - 04:07
And if neither did it would be 300
Excelchat Expert
05/09/2018 - 04:07
Okay, I got it. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
Excelchat Expert
05/09/2018 - 04:07
Please give me a few minutes.
User
05/09/2018 - 04:07
No problem, thanks
Excelchat Expert
05/09/2018 - 04:08
Try it please.
Excelchat Expert
05/09/2018 - 04:08
I used an IF() function to determine if a date is in C and E.
Excelchat Expert
05/09/2018 - 04:09
Will column C and E only have dates?
User
05/09/2018 - 04:09
Ah brilliant, I was doing SUMIF and couldn;t get it
User
05/09/2018 - 04:10
Yes they will
Excelchat Expert
05/09/2018 - 04:10
Alright then we don't really have to check for a date, we can just check if the paid date column is empty.
Excelchat Expert
05/09/2018 - 04:10
Would there be anything else that I can help you with regards to the original question?
User
05/09/2018 - 04:11
Ok perfect, no that was everything thank you
Excelchat Expert
05/09/2018 - 04:11
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
05/09/2018 - 04:11
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
05/09/2018 - 04:11
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
05/09/2018 - 04:11
Thanks again, bye
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
