Hi everyone,
I am having trouble with a formula in excel 2010.
I have a cell (F657) that I want to be equal to either cell F340 or F235, depending on which of these two cells is not blank.
Both F340 and F235 are dependent on other cells (F351 or F247) and are blank if those cells are empty.
Can anyone provide me with the right formula to do this?
I hope my explanation is clear enough.
Many thanks in advance!
Last edited by Wiggle12; 09-16-2015 at 09:11 AM.
Hi,
Assuming that one will always be populated and the other always blank:
=MAX(F340,F235)
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Hi sweep,
Thank you very much for your quick reply!
Initially both cells will be blank. Then based on the information that is added to the excel document certain cells will contain values.
Do you know how I can change the formula so that F657 will be blank if F340 and F235 are blank?
Furthermore, in F656 I will have to insert the WORKDAY formula. So F656 will be equal to WORKDAY(F657;-E657;F13:F25) if F340 contains a value and otherwise be equal to WORKDAY(F235;-E235;F13:F25) if F235 contains a value.
Do you know how I could best formulate that?
Yes, like this:Do you know how I can change the formula so that F657 will be blank if F340 and F235 are blank?
=IF(AND(ISBLANK(F340),ISBLANK(F235)),"",MAX(F340,F235))
What's the requirement if both F340 and F235 contain values?F656 will be equal to WORKDAY(F657;-E657;F13:F25) if F340 contains a value and otherwise be equal to WORKDAY(F235;-E235;F13:F25) if F235 contains a value.
Hi sweep,
Thank you!
There is no possibility for both F340 and F235 to contain a value. There will always be one that is blank.
Right now the value in cell F657 is saturday 0 january 1990. Cell F340 and F235 are blank, but do contain a formula. Do you know what I should change about the formula to have cell F657 be blank when cell F340 and F235 are blank?
Can you try these?
F656=IF(NOT(ISBLANK(F340)),WORKDAY(F657,-E657,F13:F25),WORKDAY(F235,-E235,F13:F25))
F657=IF(AND(F340="",F235=""),"",MAX(F340,F235))
The first one does not work. The second one does!
Ok, so we're getting there......when you say it doesn't work, what does it do?
Hi sweep,
Sorry for the late reply.
It says #VALUE!
Very odd. It works for me. That suggests that there is non numeric data in one of the referenced cells.
What do
=WORKDAY(F657,-E657,F13:F25)
=WORKDAY(F235,-E235,F13:F25)
give you?
What values are in cells F657,E657,F13:F25,F235,E235?
I missed a notification for this.
Those formulas also give me #VALUE!, which I don't understand because they worked just fine before.
All of the relevant cells in E and F (will) contain dates. F13:F25 represent the holidays we have. Any cell in column F represent a deadline, whereas any cell in column E represents the number of days. F657 is equal to the date in F235 or F340. F656 will equal the date represent in F657 minus the number of days taken (represented in cell E657).
Sounds like you have text in one of the cells. Can you post the sheet so we can take a look at it?
Hi sweep,
Unfortunately the sheet is confidential so I can't post it. I just checked again, and I don't have any text in these cells, so I have no idea what the problem is..
I'm not sure I can help any further in that case. As a final investigation, you could use the =ISNUMBER() function to test all the cells.
Alright, thank you. I will give it a try!
Yes they do, becauseOriginally Posted by sweep
If a cell contains a formula, it CANNOT be blank.
A formula returning "" is NOT blank. It's a TEXT string
So If F657 contains
And both F340 and F235 are "", then that formula also returns ""
So this will error because ISBLANK(F340) is FALSE.
So it uses F657 in the Workday. F657 = "" as well, so the Workday function is #Value! error.
Try
=IF(COUNT(F340,F235)=0,"",IF(ISNUMBER(F340),WORKDAY(F657,-E657,F13:F25),WORKDAY(F235,-E235,F13:F25)))
Thank you Jonmo1, that works!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks