If someone sends you a file with everything pasted as values, is there a way to find out the formulas they used? solved
It would make my life a lot simpler if people could leave formulas as is in their sheets so I don't have to do the math every time. If people paste everything as values, is there a way to find out how the values were calculated?
Thanks
213
u/excelevator 2832 12d ago edited 12d ago
Yes, you ask the person who sent it how they did it.
A very strange question indeed, shouldn't this be directed to Shirley in accounts ?
36
u/Electrical_Carry_825 12d ago
Shirley can't be serious though
25
3
u/NoSleepCrew 12d ago
I said this to a friend’s wife when I first met her. Her name is Shirley and I’m fortunate to be alive. I had a little laugh and promise me to never do it again. Then my wife and I babysat their kids and I was forgiven.
1
60
u/realmofconfusion 11 12d ago
No way. Say you get a value of 10…
Is it 0+10, 1+9, 2+8, 3+7 (and so on for all the positive single-digit integers).
Maybe it’s 100-90, or -17+27, or 68+7*9, or 37.5-32.5
If you know some general rules about the calculation you can narrow down the possibilities a little (e.g. the first set of possibilities I listed: number is always the sum of 2 single-digit positive integers) but there’s no way to know for sure.
Without knowing any calculation parameters, the possibilities would be infinite.
17
u/djprofitt 12d ago
2*5, 10/2, hell it could be COUNT
Best OP can hope for if they can’t ask the person who created the formula is hope it’s hidden somewhere in the spreadsheet
7
23
u/christian_811 12 12d ago
Not unless you have access to an older version of the sheet. If you don’t have that, the best you can do is ask them or make guesses and test them.
20
u/Dav2310675 15 12d ago
Oh dear God. No.
I got lumped with a file like that and spent months reverse engineering it. The guy who put together the spreadsheet had changed jobs and then passed away. There was very little documentation and I had the source files, but it still took a long time to work out how he put the workbook together as well as the calcs.
To be fair, he didn't do it out of malice - it was a result of file size and the calcs. Even today, if I open that file, it takes a few minutes to open.
I feel your pain.
Document your work as you go. It may save you from rebuilding it all again down the track, or at least being that guy.
26
u/No-Mechanic6069 12d ago
Changed jobs and passed away? He really had it in for you.
6
u/Dav2310675 15 12d ago
He didn't even know me.
I came to my unit about 3 years after he died, and he worked in another unit that created that workbook (and had no idea how he did it).
Sucked all round.
But I especially hated the work colleague who said "We need to update that spreadsheet. Dav2310675 can do it - he knows Excel".
There's a special place in hell for people who throw people under the bus like that...
3
u/MotherGiraffe 12d ago
This is why I always save a version with calculations and a version with pasted values that I actually distribute
3
u/Atiggerx33 12d ago
I have 1 spreadsheet like that because if I leave it all as formulas then excel starts lagging like crazy after a few thousand rows.
The bottom row though always has all the formulas (if you want to hide them you could put the formulas in the total row then you should be able to shift+ctrl+t to hide/unhide the formulas).
2
u/Dav2310675 15 11d ago
That's not a bad approach.
The spreadsheet I had, used census data and was updated every five years. The ability of Excel (and desktop hardware to process) changed a lot between when it was last done (and that had been skipped one census because no-one could do it and so it wound up being 10 years).
It is amazing tp be so used to Excel usually opening workbooks quickly, then seeing this one take a few minutes as tens of thousands of calcs get recalculated.
I haven't been asked to do it again (yet) and its overdue as another census has had its data released. But if I do, I'm thinking of using Power BI to see if that will help with processing speed.
It should definitely help keep my sheets from having the weightings used in formulae changed by the users and then hearing complaints of how the numbers are different. But that's a whole 'nother issue.
1
u/Atiggerx33 11d ago
I also do a lot of hiding of columns that shouldn't be messed with (and highlight them red incase they become unhidden as a final "don't fuck with this column!" warning).
15
u/HarveysBackupAccount 19 12d ago
How often are people are sending you files without formulas?
Do they paste everything as values or do they send you CSVs? A CSV will never have formulas
My wife's coworker at one point couldn't figure out why Excel never saved the formulas and formatting she added, so she had to redo it every time. And so that's what she did, for months and months.
She made some offhand comment about it and my wife realized that this woman would open their CSV data files, do all the work, and then save it as a CSV again. And she just thought her PC or her version of Excel was broken and kept doing it. It was a big day for her when my wife helped her figure that out.
3
u/drLagrangian 1 12d ago
It can happen accidentally when you send a file that has links to other workbooks, then break the links.
3
u/HarveysBackupAccount 19 12d ago
really? that will actually replace formulas with their values? I had no idea
5
u/drLagrangian 1 12d ago
It should only happen if you choose to break the links on purpose (links are on the data tab).
But I'm pretty sure excel is haunted so it may also do it in its own sometimes.
1
u/SneezyAtheist 1 12d ago
Only if it's a formula linking to the other book. That cell will be changed to value.
Any formulas not linked to other files will remain.
3
u/cqxray 48 12d ago
Some groups send out Excel files as values or as PDF because they want to be proprietary with their model’s formulas and/or they want to make sure no numbers are changed later on by someone else.
1
u/HarveysBackupAccount 19 12d ago
Fair enough
I guess I assumed these are OP's coworkers who are not intentionally trying to hide anything (except out of pettiness or spite?)
1
u/Elleasea 21 11d ago
Also to prevent someone accidentally adjusting a formula or an input and then having incorrect results.
8
5
u/hasanyoneseenmyshirt 12d ago
I fucking hope not. 90 percent of my inventory count sheet is the RANDBETWEEN() function.
3
u/Euphoric-Brother-669 1 12d ago
Daft question - who knows what is in the data.
I have created many Price Lists for Clients, I might have a number items the company sells and manufactures. I will have a lot of calculations to work out materials and time etc and of course a mark up. When done, that is sent to an output sheet. This sheet I will copy and paste as values to make it dumb. Product X for a quantity of 1000 is $xx and so on. That is all they need to know.
When a revision or addition is required I can redo the price list relatively easily but they still get a dumb sheet back.
Formulas may contain proprietary information like mark ups, it may be that you have asked a professional for a set of calculations say for stress testing or weight limits or health and safety, that is their expertise, all wrapped up in a single output that says, you need a steel of this thick by this deep by this long. They may be thinking pay once, get the information, do it myself again for free.
3
u/Southern-Bother-1350 12d ago
Exactly. I am working as a valuation analyst, when we are done with our valuation exercise (which have a lot of complicated formulas) we submit our deliverables as paste as value excel sheet. If the Client figures out how we do our work, then we will go out of business😅
2
u/plusFour-minusSeven 4 12d ago
When you say "do the math", what do you mean? Haven't the formulas already taken care of the math?
2
u/Whole_Mechanic_8143 8 12d ago
Not when they then hardcoded the results. It's the equivalent of getting the file as a pdf printout.
2
u/plusFour-minusSeven 4 12d ago
Still a little lost. Are you doing QC on them and need to see their work to verify it's valid?
1
2
u/zeradragon 1 12d ago
You should ask the person to either send you the back up for the figure you have questions on or ask for the non valued version because there's no way for you to reverse engineer the formula purely based on values unless you have some knowledge of how to tie things together yourself. The other person should have a working version and a distribution version; if they only kept the valued version, then they fked up because there's no audit trail there.
2
u/Grumpydeferential 12d ago
I read that ChatGPT released a new version yesterday that is supposed to be more powerful with calculations. Maybe a long shot, yet I wonder if you could upload your spreadsheet to have ChatGPT determine formulas that might make sense given the outputs?
3
u/RandomiseUsr0 4 12d ago edited 11d ago
Funny, I was going to suggest using a statistical analysis approach - testing the hypothesis that the numbers have got things in common, should be able to work out, given enough samples
[edit] of course, should have said, assuming that the numbers have nothing in common and then trying to disprove that hypothesis - it’s an important angle one must hold in mind
1
u/originalusername__ 12d ago
Think about it this way, if this was possible anyone could reverse engineer any spreadsheet they were sent and find all sorts of proprietary data.
1
u/ContemplatingPrison 12d ago
Only if you can access the file on a sharepoint. Then you can lolk at version history of the document
1
u/Newplasticactionhero 12d ago
I’m gonna throw in with everybody saying ask the person who sent it. If my back was up against the wall, I would ask ChatGPT to analyze it for an answer. Not sure how well that would work, but it’s worth a try.
I can understand why people just send out workbooks with pasted values instead of formulas though. It really does reduce file size.
1
u/JoeDidcot 53 12d ago
Does the filename change? If within an organisation, try searching on the shared drive for the original file.
1
u/Mr_frosty_360 11d ago
Sounds to me like either they did the work in another worksheet or calculations and filling data was done through VBA.
1
2
u/Bingemann 11d ago
I have the same problem with my power company. They bill me for the one hour I had the maximum power usage for a month. I have the same numbers as they have but my value are 0.005 kW different from theirs. Annoying.
•
u/AutoModerator 12d ago
/u/dumbfly - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.