In B7: =ROUND($B$1*B3/SUM($B$3:$B$5),2)
B8: =ROUND($B$1*B4/SUM($B$3:$B$5),2)
B9: =B1-SUM(B7:B8)
the formula in B9 just ensures there are no rounding issues.
Have been testing this for a while and is working exactly like we wanted, thanks for your help on this.
That doesn't really make sense. You have to do something about them or your totals will be too high/low. The question I asked is what do you want to do about them? Given the example you posted, what should be the outputs in each cell?
Sorry, if the amount is over then the pro-rata method you mention above works perfect, if the amounts are under then it needs upping to 100%.
How do you want to allocate any over/under amounts?
Basically ignore them.
Hi,
When you say :
Do you mean a Percentage of each Percentage ???
No a value, so for example 60% of £81,000 = £48,600
Hi All,
Struggling with a formula if any body could help please?
What I need is a formula for cells B7, B8 & B7 and the criteria is as follows:
Values in these cells needs to be the percentage of the values in B3, B4 & B5 respectively, which is the easy bit. The total sum of cells B7, B8 & B7 must not exceed 100% of the value of cell B1.
Also if the sum of cells B3, B4 & B5 add to less than 100% then cells B7, B8 & B7 still need to total B1.
So in summary B7, B8 & B7 always need to add up to B1 but split by the percentages of B3, B4 & B5, which can be under or over 100%.
Thanks to whoever accepts the challenge!!
From what I've heard, the latest build seems to now correct most of the weird errors that people had been getting.
Hi,
Yes all seems to be back to normal now.
Quick question - are you on version 2212 by any chance? I've seen quite a few reports recently of weird errors with workbooks with userforms on that build. There is apparently a fix in the works.
Hi Yes,
We are on version 2212.
It is rare indeed that that line would cause an error. It is more likely to be code in the Initialize or Activate event of that form.
Thanks,
Could you explain a bit more? How would this suddenly affect 100's of workbooks if nothings changed?
I have now narrowed it down to the userform1.show. I have managed to force open the book in safe mode and disabled the command so the userform1 does not show on start up.
Is there something in the background that can cause this as it is odd, as if the userform1 is then called via a macro when the book is open it appears no problem.
Feel free to comment
Hi, Sorry only just back in work, will have a look at the link you provided now, thanks.
Hi,
I have some VBA & User forms in an Excel Book that were written in 2005, everything has worked fine on every upgrade to Excel and on 365 up until last week.
Upon trying to open the workbook it will not open and when I can eventually get to the debugger it comes up with the attached.
With much fiddling and getting it into safe mode and I disable user form1 it seem to open fine, I can then manually bring up user form1 OK.
My issue is there are hundreds of spreadsheets in use that now won't open, any ideas what may have gone wrong and a fix?
Thanks for your help.
