Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

how to make a format that if multiple cells have number contents its value is 1 in the last cell
Solved by C. C. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/10/2017 - 12:41
Hello, I understand that you need help with checking if multiple cells have numbers, right?
User 19/10/2017 - 12:42
kind of
User 19/10/2017 - 12:42
can I show you what I mean?
Excelchat Expert 19/10/2017 - 12:42
Okay, can you give me an example of how your data looks like and the output?
User 19/10/2017 - 12:42
yes
Excelchat Expert 19/10/2017 - 12:42
That way I'll have a better understanding.
Excelchat Expert 19/10/2017 - 12:42
Please use the Document Preview to the right.
User 19/10/2017 - 12:44
actually it might be easier if I copy and paste a spread sheet
Excelchat Expert 19/10/2017 - 12:44
Okay, go ahead please. Just note that our time is limited.
User 19/10/2017 - 12:45
so I want the column R to equal a single number not a total of numbers
Excelchat Expert 19/10/2017 - 12:45
Okay, can you give me an example of what R's value should be?
Excelchat Expert 19/10/2017 - 12:45
For instance in R6, what should the output be?
User 19/10/2017 - 12:45
to equal 1 hour or 0.5 hour
Excelchat Expert 19/10/2017 - 12:46
Okay, why is that?
Excelchat Expert 19/10/2017 - 12:46
I'll need the logic before I can build the formula. I have an idea what you mean though but I'd like to hear it from you still.
User 19/10/2017 - 12:46
so that the total at the end equals 1 job under either 1 hour or 0.5 hours
Excelchat Expert 19/10/2017 - 12:47
I'm sorry I am not understanding that part.
User 19/10/2017 - 12:47
so before I did a formula that was adding the quantities together but the total was wrong at the end
Excelchat Expert 19/10/2017 - 12:47
What should the value be in R6.
Excelchat Expert 19/10/2017 - 12:47
There should only be 1 value in R6, right?
User 19/10/2017 - 12:47
so this is what I did before..
User 19/10/2017 - 12:48
its total is 3 but I want it to be 1 as its only a "1 hour job"
Excelchat Expert 19/10/2017 - 12:48
Okay, that's what's getting me confused.
Excelchat Expert 19/10/2017 - 12:49
So the expected value in R6 is 1, right? Please answer that question first.
User 19/10/2017 - 12:49
so anything in the row 6 will always total to 1 as its time is an hour stated in column B
User 19/10/2017 - 12:49
yes
Excelchat Expert 19/10/2017 - 12:50
Are you saying that you only need to know if the time duration in column B is either 1 hour or 30 minutes?
User 19/10/2017 - 12:50
I guess yes
User 19/10/2017 - 12:51
but I only want it to come up if the cells in column D,F,H,J,L,N,P are entered
Excelchat Expert 19/10/2017 - 12:51
Can you just give me an example please?
Excelchat Expert 19/10/2017 - 12:51
I need to know the concrete rule.
Excelchat Expert 19/10/2017 - 12:52
Excel only knows TRUE or FALSE, it doesn't understand in betweens.
User 19/10/2017 - 12:53
so I want it to look like that as a total
Excelchat Expert 19/10/2017 - 12:53
Let me know if this is TRUE or FALSE.
Excelchat Expert 19/10/2017 - 12:53
As long as anyone of D,F,H,J,L,N,P has a number, then calculate the time duration in column B?
User 19/10/2017 - 12:54
yes I think so
User 19/10/2017 - 12:54
do I do something to column B?
User 19/10/2017 - 12:54
to link with R
Excelchat Expert 19/10/2017 - 12:55
I'm sorry but that won't cut it. It can't be a "I guess" or "I think so" because if I build the formula with that in mind and it turns out to be not what you think then I won't be able to solve it for you.
Excelchat Expert 19/10/2017 - 12:55
We need the concrete logic of what you want.
Excelchat Expert 19/10/2017 - 12:55
It has to be concrete before I can build the formula.
Excelchat Expert 19/10/2017 - 12:55
I hope you understand.
User 19/10/2017 - 12:55
then yes
Excelchat Expert 19/10/2017 - 12:55
Okay, I'll build the formula based on that logic.
Excelchat Expert 19/10/2017 - 12:58
A gentle reminder that since you've extended the session: Please ensure that you end the session properly later or else I'll be stuck here waiting for the timer to expire and I'll be unable to help others.
User 19/10/2017 - 12:58
ok
Excelchat Expert 19/10/2017 - 01:04
Thank you for waiting.
Excelchat Expert 19/10/2017 - 01:04
The formula in column R is done.
User 19/10/2017 - 01:04
no problem
Excelchat Expert 19/10/2017 - 01:05
Based on what we've discussed, it checks if there are quantities in your data.
User 19/10/2017 - 01:05
ok...is that with the row 10 being 0.5?
Excelchat Expert 19/10/2017 - 01:05
If there are, then it calculates the differences between Time in column B.
Excelchat Expert 19/10/2017 - 01:05
Why would it be .5?
Excelchat Expert 19/10/2017 - 01:05
B10 is 15:30-16:30
User 19/10/2017 - 01:05
as its a 30 min job not an hour like the row 6
Excelchat Expert 19/10/2017 - 01:05
Which is 1 hour.
User 19/10/2017 - 01:06
ohhhhh
User 19/10/2017 - 01:06
sorry
User 19/10/2017 - 01:06
my mistake
User 19/10/2017 - 01:06
i was meant to click on a 30 minute job
Excelchat Expert 19/10/2017 - 01:06
Try to add values in 7.
User 19/10/2017 - 01:06
so maybe row 9
User 19/10/2017 - 01:06
fantastic!!!
User 19/10/2017 - 01:06
you're a genius
Excelchat Expert 19/10/2017 - 01:06
I added some test values in row9 and it returns .5
Excelchat Expert 19/10/2017 - 01:07
Thank you for being very patient in helping me understand what you needed.
User 19/10/2017 - 01:07
so i just copy the formula over to my workbook
Excelchat Expert 19/10/2017 - 01:07
As long as the formatting is the same, it should work, yes.
User 19/10/2017 - 01:07
no problem thanks for all your help
Excelchat Expert 19/10/2017 - 01:07
https://docs.google.com/spreadsheets/d/1i8Ms7pgiiAQt5WRHjpH4kyIEe55IhlRcZf-G0QEZhHk/edit
Excelchat Expert 19/10/2017 - 01:07
You can download the sheet by going to the link above, clicking FILE > DOWNLOAD AS > Microsoft Excel (.xlsx)
Excelchat Expert 19/10/2017 - 01:08
Please download that file so you'll have a copy in case you'll need to go back to the original formula.
User 19/10/2017 - 01:08
ok
User 19/10/2017 - 01:08
great
User 19/10/2017 - 01:08
just downloading now
Excelchat Expert 19/10/2017 - 01:08
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert 19/10/2017 - 01:08
Please do not forget to click the End Session otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User 19/10/2017 - 01:08
haha yes of course! *****
User 19/10/2017 - 01:09
thanks again!
User 19/10/2017 - 01:09
have a good day!

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc