Excel - IF Function Problem - Expert Solution

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
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
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
Excelchat Expert 13/04/2018 - 04:38
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
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
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.

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
The Allstate Corporation
United Parcel Service
Dell Inc