Display DIFFERENT value than actual contents of cell?

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
62
I want to type in some code (ie .5A,.5B) but rather than display ".5A,.5B" I'd like to display "1" (which in my case is the addition of the two components). Sometime the formula might be ".25A,.25B,.5C" (1) or ".25A,.25B" (0.5), so it's not that simple. Any ideas?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
You're going to need to type that in column A and then have column B populate a new value. You can't put formulas in custom formatting.
 

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
62
Well I have kind of a hunch of how to do it, but I didn't want to lead the answer....

If I use this in conditional formatting, for formula:
=EXACT(eval(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"A/","+"),"B/","+"),"C/","+"),"D/","+"),"B",""),"C",""),"D","")),1)

I can then change the number format to:
;;;"1"

That works well when the components add up to 1. But, I'm not sure I want to manually do all the permutations for .9, .8, .75, .5 etc!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

I can't know if it is possible because your post is vague, you did not define clearly your problem.

The examples are fine, but they should help clarify the definition of the problem, not making us guess what the problem is.


For ex.

1 - Does the expression always consist of some terms separated by commas?
2 - What's the minimum and maximum number of terms in the expression?
3 - Are the possible total results a small set of values or can they be anything? (For ex. 0 to 1 in increments of .05)
4 - Can you use vba?
5 - What's your excel version?
6 - Is each term in the expression always a number followed by just 1 letter? any letter?
7 - Does the number at the beginning each term in the expression always start with a "."? If not which are the possible formats?


Well, these are just some things that crossed my mind, when I read your post.
It's not possible to know if there is a solution if one does not understand the problem.

If you can answer those questions and add anything else you deem relevant I'll give it a try tomorrow.

Nighty night!
 

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
62
Thanks for taking the time to read/help. Sorry if I wasn't clear, things always seems clear when I post ;)

A little background before I answer all your questions.
I've got a staffing spreadsheet that I track over 100 people and what their doing. Curently each person has one row with dates in columns. I've been putting their workload (in EP, or equivalent persons) to show if they are busy or need work. A value of 1 represents 1 EP which means they have full-time assignment for that date (weekending in my case). So, with 100+ people and one year's worth of weekly data (52 columns), their is a fair amount of data entry that we are doing on this spreadsheet.

Now it gets interesting...we've decided to try to dig a little deeper and understand the composition of the 1 EP value. Sometimes it's simple and 1 EP ties directly to a single job "A". Other times, a person's time is split between .5 of job "A" and .5 of job "B". I've decided I don't need to keep track than more than 4 of their possible jobs (A,B,C and D). Sometimes people aren't fully staffed, but maybe supporting 2 jobs (ie .25A/.25B), since that's only .5 they are underutilized.

So, I was hoping to be able to enter .25A/.25B in the cell and have it display .5

Now, on to your specific Q's...
Hi

1 - Does the expression always consist of some terms separated by commas?
I control this, I think I'll use a "/" forward slash instead of a comma. It will be consistant

2 - What's the minimum and maximum number of terms in the expression?
It's percentage of a person's time when supporting multiple jobs. I think 25% increments would be good enough, but I have done 10% and 90% in the past. Maybe I just limit it to 25% increments to make the problem easier.

3 - Are the possible total results a small set of values or can they be anything? (For ex. 0 to 1 in increments of .05)
See above.

4 - Can you use vba?
I can, and am currently using VBA on the spreadsheet now for many other aspects. But, this issue is really on the "simplier" portion of the spreadsheet which is just data entry of people's time.

5 - What's your excel version?
2010


6 - Is each term in the expression always a number followed by just 1 letter? any letter?
Again, I'm choosing this syntax. # followed by A,B,C, or D

7 - Does the number at the beginning each term in the expression always start with a "."? If not which are the possible formats?
Yes, always fractions of 1 person

Well, these are just some things that crossed my mind, when I read your post.
It's not possible to know if there is a solution if one does not understand the problem.

If you can answer those questions and add anything else you deem relevant I'll give it a try tomorrow.

Nighty night!

By the way, if I limit myself to 25% increments, then the solution I posted above will work if I'm willing to create CF for .25,.5,.75, and 1 uniquiely....possibly the best solution?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
OK

This is a solution without vba, considering the delimiter forward slash "/" , .25 increments and the letters ABCD.

For this example I selected A2, and added

Condition 1:

Used in the conditional formatting the formula:

=SUMPRODUCT(0+RIGHT(SUBSTITUTE(LEFT(A2&"/0A/0B/0C/0D",FIND(MID("ABCD",COLUMN(INDIRECT("A:D")),1),A2&"/0A/0B/0C/0D")-1),"/","000"),3))=0.25

and then defined for this condition the number format:

;;;"0.25"

Condition 2:

Used in the conditional formatting the formula:

=SUMPRODUCT(0+RIGHT(SUBSTITUTE(LEFT(A2&"/0A/0B/0C/0D",FIND(MID("ABCD",COLUMN(INDIRECT("A:D")),1),A2&"/0A/0B/0C/0D")-1),"/","000"),3))=0.5

and then defined for this condition the number format:

;;;"0.50"

Condition 3:
Same as before but for 0.75

Condition 4:
Same as before but for 1


Copy the format down.



I posted in column A the text shown in the cell with the conditional formatting. In the cells to the right are the respective values displayed in clear.

Please test


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.75</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.25A/.25B/.25D</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.25</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.25A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.5B</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.75</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.75C</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.5D</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.75</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.5A/.25D</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.75B/.25C</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>9</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.75</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.25A/.5C</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>10</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">.5A/.5D</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>11</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1C</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>12</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>
 

Watch MrExcel Video

Forum statistics

Threads
1,127,844
Messages
5,627,219
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top