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.