**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.*