Question: Easy way to calculate percentage in Worksheets

[Tobias Kern]

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

[Pat Stanford]

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

 

[Tobias Kern]

Hi Pat,

 

greetings and thnx for your help and reporting VB-191196.

Hope we get a fix soon!

 

Please support my wish: VE-103900

 

Greetings from Germany and have a good start in the week.

Tobi