Masking numbers from 0 to 100

Saqib Husain, Syed
Saqib Husain, Syed used Ask the Experts™
on
I need to fill in staff performances as a percentage while other people would be looking on. In order to prevent them from knowing what I have entered I would like to mask these numbers by keeping them as percentages but with a random value. The real value and random value should have a one-to-one relationship. That means that

33% may change to something like 62% using a formula and reversing the formula should give back 33% corresponding to 62%.

Can we have a formula (not a lookup table)?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi, ssaqibh.

Not exactly what you asked for, but...
Change the cell's formatting to show zero decimals and use the following...
=RANDBETWEEN(1,100)+A1

To reverse it, just drop the integer portion.

Regards,
Brian.
Looks like this might work. But I shall wait for a more direct answer.

Thanks

Saqib
If it's going to be a 2-way conversion (i.e., 33 always gives 62, and 62 always returns to 33, or any other pairs of numbers), why wouldn't a "lookup table" work? Results would be exactly the same. Are you thinking it should be a different pair for each individual? All that should take would be a different 'offset' into the table.

But I like Brian's suggestion.

Tom
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

ssaqibh,

Closer to what you requested, but sillier than my first suggestion...
Obfuscate...
=(MID(SQRT(A1)&REPT("0",10),5,5)&MID(SQRT(A1)&"00000",3,2)&INT(SQRT(A1)))/10^8+1/10^9
Deobfuscate...
=((MID(B1,10,1)&(MID(B1,7,2)&MID(B1,3,5)))/10^7)^2

Slight drawback is that 0%, 1% and 100% all obfuscate to 0% (but deobfuscate correctly - it's just a rounding issue.)

(Do you have any issue with the original that could be corrected?)

Regards,
Brian.

Regards,
Brian.
ssaqibh,

One of the problem with a truly random display is that there's nothing to alert you that you've mistyped a percentage (say 19% instead of 91%). The formula below uses the units position to give you an idea of the size of the actual % (so, 1 means it's in the 90's, 2 it's in the 80's etc.). The tens position remains random.
=RANDBETWEEN(0,9)*10+(10-INT(10*A2))+A2

Regards,
Brian.
Most Valuable Expert 2014
Top Expert 2015
Commented:
how about something like
mask = (percent*61+17) modulo 101
percent = (mask*53+8) modulo 101
though it's not very secure
Perfect. Just what I was looking for.

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial