Jump to content

Question: Easy way to calculate percentage in Worksheets


Recommended Posts

Hi and greetings.

 

At the moment, I try to find a more easy way to calculate a relative percentage

in a Worksheet without of using criteria in a cell.

 

Attached is a small file with a use case.

 

B3 list the area of the listed rectangles.

B4 = formula with criteria

In C3 I want to have the percentage of the area of one rectangle, relative to the summarized area of all rectangles.

 

I only got my percentage, if I set criteria in B4 and calculate with it.

Otherwise, it won't work.

 

For this relative easy calculation, this solution works "okay".

For more complex calculations (complex if-formulas), this could be a tricky one.

 

Another solution would be to use two worksheets and reference

some cells with each other.

 

I want to know if there is more easy way without, of a use of criteria or a use of two worksheets?

 

like:

C3 = B3 / B4

B4 should list the summarized area in a way, that it is possible to calculate with it in C3

without a criteria.

 

If not possible, I will write a Wish in Jira.  

 

Greetings, and thanks for your help

Tobi

23-01-14 Percentage.vwx

Link to comment

I think there is a bigger bug and I think I have reported it previously. It appears that 2023 (through at least SP2) can not handle a database calculation that references a different part of the database.

 

You should be able to do something like:

 

Set Cell B1 = B3 to grab the total from the sum at the top of the database and then use a formula of =B3/B1 to get your percentage.  

 

When you try and do this, B1 displays the proper value when first entered, but when you add the B3/B1 formula, the value of B1 resets to zero.  😞

 

This appears to happen anytime you use a referenced cell back into the database.

 

It looks like your using criteria is the only current work around.

 

You can simplify your criteria. you don't need the Database and in most cases you don't need the DLVP criteria. So your original of:

 

=AREA(DATABASE(NOTINDLVP & (NOTINREFDLVP) & (T=RECT)))

 

Can become just

 

=AREA(((T=RECT)))

 

While they are getting better, worksheets still need some more love.

 

And I did file this before:

 

10/21/22 VB-191196. Spreadsheet Cell Value read and displayed as zero when used in database header calculation

 

  • Like 1
Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...