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.
