**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need both to fit together.
-A manual entry of 1 equals 11, if manual entry is less than 1 equals 5.5

Solved by G. B. in 23 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
13/04/2018 - 04:35

Hi

Excelchat Expert
13/04/2018 - 04:35

hello there!

User
13/04/2018 - 04:35

I can't fit both formulas in one field

Excelchat Expert
13/04/2018 - 04:36

Let me help you with that! :)

User
13/04/2018 - 04:36

If(1,11>0.5,5.5)

User
13/04/2018 - 04:36

this is the just of what I am trying to do, but it does not work

Excelchat Expert
13/04/2018 - 04:36

Hmm. It seems wrong. Let me fix it for you! :)

User
13/04/2018 - 04:36

The only entries are 1 or 0.50

User
13/04/2018 - 04:37

Yup, it is wrong, lol

Excelchat Expert
13/04/2018 - 04:38

I've made a sample in the document preview. Can you see it?

User
13/04/2018 - 04:38

No

Excelchat Expert
13/04/2018 - 04:38

=if(A2>=1,11,5.5)

Excelchat Expert
13/04/2018 - 04:39

Do you have a working file?

User
13/04/2018 - 04:39

Le me try it

Excelchat Expert
13/04/2018 - 04:39

You can change A2 with the cell range of your manual entry.

User
13/04/2018 - 04:40

It<s giving me a circular reference error

User
13/04/2018 - 04:40

And yes, I did change the A2 to correct cell

Excelchat Expert
13/04/2018 - 04:40

Can you upload in here your working file?

User
13/04/2018 - 04:40

Probably not, sorry

User
13/04/2018 - 04:41

What these are are 1 equals a shift of 11 hours and 0.5 equals a half shift of 5.5 hours

Excelchat Expert
13/04/2018 - 04:41

oh okay then. What's the cell range of your manual entry?

User
13/04/2018 - 04:41

On a row, it can vary from one cell to the next

User
13/04/2018 - 04:42

B34

Excelchat Expert
13/04/2018 - 04:42

and where do you wish to display the shift of the manual entry?

User
13/04/2018 - 04:43

in the same cell

User
13/04/2018 - 04:43

So, the entry 1 would change to 11 and the entry 0.5 would change to 5.5

User
13/04/2018 - 04:44

I can do it easy with just one formula, but I can<t add the second funcion

Excelchat Expert
13/04/2018 - 04:44

I see. That's actually not possible actually. The resulting "Shift" should be placed somewhere, preferably beside the the manual entry cell.

Excelchat Expert
13/04/2018 - 04:45

Like if you want to put the "Shift" next to B34, which is C34, you need to copy the formula "=if(value(C34)>=1,11,5.5)" in C34.

User
13/04/2018 - 04:45

That's a bummer, as I will need to manually change each cell formula that corresponds with the entry.

Excelchat Expert
13/04/2018 - 04:45

Sorry, that's supposed to be "=if(value(B34)>=1,11,5.5)"

Excelchat Expert
13/04/2018 - 04:46

you can drag down the formula once you copied it to C34. It will automatically adjust the B34.

Excelchat Expert
13/04/2018 - 04:46

So if you drag down the formula in C34 down to C35-C40, it will automatically change the reference cell to B35-B40.

User
13/04/2018 - 04:47

So basically I have add a row or a column

User
13/04/2018 - 04:47

I can't be done within one cell?

Excelchat Expert
13/04/2018 - 04:47

Yeah, you will need to add a column (preferably) right beside the the B column.

Excelchat Expert
13/04/2018 - 04:48

It can't be, unfortunately. that would give you the "circular reference" error.

User
13/04/2018 - 04:48

Well, well, no wonder we could not figure it out

User
13/04/2018 - 04:48

Hard to believe though, like it seems simple enough

Excelchat Expert
13/04/2018 - 04:49

Yeah, it may sound somewhat simple, but Excel will need a constant and non-circular reference to do its calculations.

User
13/04/2018 - 04:50

It is probably quicker to change make a formula for each and then copy it in its applicable cell

Excelchat Expert
13/04/2018 - 04:51

We can do that with the added column. As explained a while back, once you've established the formula for a single row, you can drag it down and it will automatically adjust the reference to its applicable row.

User
13/04/2018 - 04:52

Yeah, but in a row there are different entries

Excelchat Expert
13/04/2018 - 04:53

Are you not able to upload your excel file due to confidentiality reasons?

User
13/04/2018 - 04:53

Exactly

Excelchat Expert
13/04/2018 - 04:53

I see. Can you provide a sample cell with different entries?

User
13/04/2018 - 04:54

Sun Mon
11-Mar 12-Mar
Sean 1.00 1.00
$- $-
John 1.00 0.50
$- $-

Excelchat Expert
13/04/2018 - 04:54

Oh, and that is contained within one cell?

User
13/04/2018 - 04:54

Btw, I'm gonna run out of time, I have a conference call for 2pm, so if you notice I'm not responding that would be why.

User
13/04/2018 - 04:55

No, its different cells. 1.00 is in one cell, 0.50 is in the next column

Excelchat Expert
13/04/2018 - 04:55

Okay, noted on that.

Excelchat Expert
13/04/2018 - 04:55

I see. We just need to reference the formula on a single cell (on where it would either be 1 or 0.5).

Excelchat Expert
13/04/2018 - 04:56

Like if the 1 or 0.5 is located in C2, then we could add a new column (column D) and paste the following formula to D2: =if(value(C2)>=1,11,5.5)

User
13/04/2018 - 04:57

Can I print our discussion?

Excelchat Expert
13/04/2018 - 04:57

Yes you may. :)

User
13/04/2018 - 04:57

I don<t know how, I pressed on print, it is shows the spreadsheet only

User
13/04/2018 - 04:58

Sorry, I am running out of time

Excelchat Expert
13/04/2018 - 04:58

you tried ctrl+p?

User
13/04/2018 - 04:58

ok, I tried, hopefull its all of our discussion

User
13/04/2018 - 04:58

thanks, and have a great day, Appreciate you trying to help me

Excelchat Expert
13/04/2018 - 04:59

Thanks as well! You can end the session on your end btw. Have a nice 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.*