Display DIFFERENT value than actual contents of cell?

[insaneoctane]

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?

 

[NeonRedSharpie]

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]

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]

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]

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]

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>