Databases
StackOverflow2013
Postsdbo
POVBA Excel Percent format in an array
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA Excel Percent format in an array
    primarykey
    data
    text
    <p>Is there a way to save a specific format in an array? I'm trying to find the percent difference and the regular difference between numbers in two separate grids. I can do these things individually but when I try to put the answer in the same worksheet I run into errors.</p> <p>As of now, I calculate the percent difference and then I select the grid where all my answers are and change the style and number format. However, when I try to show the difference as well (such as "percent difference / difference" the program returns a long decimal for the percent difference. I know that the reason is because the number format and style format are not saved into the actual array but I can't figure out how to fix it.</p> <p>I've tried the following:</p> <ul> <li>Changing the format right before I create the slash and the difference</li> <li>Assigning the answer array to the current values in the active cell (the values are still the decimals, not the percent counterparts)</li> <li>Calculating percent difference and the difference, then changing the format (this was more of a faint hope than an actual attempt to change anything)</li> </ul> <p>I can't remember if I tried anything else but I know it failed horribly.</p> <p>***** Example ******</p> <p>The user will send my program to a specific workbook that has two sheets with a grid of numbers on them. These grids have the exact same dimensions. My program will calculate the difference between these sheets (Sheet1 - Sheet2), the percent difference ((Sheet1 - Sheet2) / Sheet2), and both. These three calculations are presented on three separate sheets that the program creates. All of the numbers that the program produces will be in the cells that correspond to the cells where it got the numbers in the first place. Also, the third sheet (both percent and difference) are seen as "percent difference / difference". Now here's an example with test numbers.</p> <p>Grid 1</p> <pre><code>12.0 5.0 2.0 10.0 </code></pre> <p>Grid 2</p> <pre><code>6.0 7.0 1.0 3.0 </code></pre> <p>Diff</p> <pre><code>6.0 -2.0 1.0 7.0 </code></pre> <p>Percent Diff</p> <pre><code>100.00% -28.57% 100.00% 233.33% </code></pre> <p>So the "Both" tab would look like:</p> <pre><code>100.00%/6.0 -28.57/-2.0 100.00%/1.0 233.33%/7.0 </code></pre> <p>Sorry for not making this more grid like. I wasn't sure how to present it.</p> <p>Thank you</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZach Saucier
    1. USJesse Smothermon
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIt sounds as if you are exporting the array and the string for each item is a percentage value, followed by a '/' and then the value is that what you mean? e.g. ".05%/25" Are you trying to store these as values that can still be used in calculations as well as presenting them combined?
      singulars
      1. POVBA Excel Percent format in an array
      1. USdatatoo
    2. COPlease show us an example so we don't have to guess what exactly you are trying to accomplish.
      singulars
      1. POVBA Excel Percent format in an array
      1. USJean-François Corbett
    3. COI added an edit, sorry the grids look a bit weird in the example but pretty much if there's a space then it's in a different cell. Thank you for responding
      singulars
      1. POVBA Excel Percent format in an array
      1. USJesse Smothermon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload