Formulas in tables

Hi,

I tested your workaround, but it doesn’t seem to work. If I use =round(A1/B1+0.01,2), it rounds the value to 2.01. But if I use =round(A1/B1+0.001,2), it rounds it back to 2 (without the .00). It seems that the round-function “cuts” the result in someway and doesn’t take in consideration the “third” decimal value.

I also tested to use brackets in the formula, like =round((A1/B1)+0.00001,2) and =round((A1/B1+0.00001),2) without any luck.

Hi,

mmh, ok, then the math library probably cuts off .00, even after rounding.
Unfortunately, I don’t know a solution to this then. Sorry ??

Regards,
Tobias

I exported my table in .csv format to open office. Used “find and replace” successfully to remove “ozt” from 2800 entries. Thank you so much for saving me time with this issue. I gave you 5 stars and left a review.

Did you have any ideas on how to bulk edit a column. I need to add the following expression to Column D for 2800 entries for silver melt value chart:

=round(C2*0.925*0.90*E2,0)
=round(C3*0.925*0.90*E2,0)
=round(C4*0.925*0.90*E2,0)
=round(C5*0.925*0.90*E2,0)

…and so on up to C2800

Any suggestions are greatly appreciated! If not, it’s going to be a long day.

Hi,

thanks for the review, that’s very nice!
And good to hear that the idea with the CSV export saved time! ??

With the bulk edit: No, I don’t really have an idea for that, except maybe trying to write a macro in Visual Basic that does this. In Excel that should be possible, but I don’t know if Open Office has this function, too. And unfortunately, I have never really worked with macros, so I don’t know where to start with that ??

Best wishes,
Tobias

I was able to autofill mathematical formula in open office. So Column D has:

=round(C300*0.925*0.90*E2,0)

I did this to eliminate “copy and paste” function for 2800+ entries. But now I need to manually change :

C300
C301
C302
C303
C304

…and so on up to C2864

Is there a way to do this instantly?

[signature moderated – please don’t add unnecessary links to your site]

Hi,

well, manually changing this is basically the same as filling the content in, from a technical point of view. So, there’s still no direct and easy way to do this.

However, I have an idea:
Go to https://writecodeonline.com/php/ and run this code:

for ( $i = 1; $i <= 2800; $i++ ) {
  echo '=round(C' . $i . '*0.925*0.90*E2,0)'."<br />";
}

This will give you a long list of the strings that we need. Copy this (start with a small chunk first) and paste it into Open Office. That should add one line per cell.

Regards,
Tobias

Thanks for the suggestion. It almost worked but not quite. https://writecodeonline.com/php/ is a very powerful website. Thanks for that link. I used the following code for a small batch:

for ( $i = 2605; $i <= 2779; $i++ ) {
echo ‘=round(C’ . $i . ‘*0.925*0.90*E2,0)’.””;
}

Then I pasted the results into open office spreadsheet for C2605 – C2779. I used the “paste special” function for unformatted text. Used Western Apple separated by a comma.

After pasting, it yielded the results of the formula instead of the actual formula. So it performed the mathematical computation. But I need the actual formula as text instead of a computation.

So it almost worked. Thanks again for the suggestion. Looks like I will have to input manually.

[signature moderated]

Hi,

ah, ok. That’s unfortunate ?? I had hoped that it would input it as text.
But not everything is lost then!
You can try with a trick: The important character for the formulas is the =. So, just use somthing like #=# (in the PHP code). Then insert that new chunk of lines. That will insert it as text. Then save to CSV, open the CSV file in a text editor again, and search/replace #=# with =.

Regards,
Tobias

Thank you for the tip. I will try this.

I followed your instructions. Very smart idea. I finally have the code exactly right for each cell except for:

1101 – 2427 need = in front of the code. I tried the find and replace but it won’t allow me to enter =. It just shows up as an error. But your method still saves me quite a bit of time. All i have to do is enter = for each entry instead of numbering each one. Thanks for the help!

Hi,

good to hear that this works!

Now, I’m not sure what exactly is not working with 1101 to 2427? I don’t understand…
And what error are you seeing?

Regards,
Tobias

Tobias,

Finally, finished my project. Thanks for all of your help! I was having issues in open office after I copied over the formula from https://writecodeonline.com/php/. I was getting the following error message:

Err: 508

I had to manually enter = for my entries. Open office would give an error message if = was copied into the cell. The cells had to begin with round. If they begin with = then there is an error 508.

I tried special pasting. But it just calculated the value without keeping the formula.

Hi,

great to hear that you finished this tedious task! ??

Yes, that = is necessary for formulas, but that’s why my suggestion was to not enter it in Open Office, but via search/replace in a text editor, after exporting the table to CSV.
But as you are finished now, that’s no longer necessary, I guess ??

Best wishes,
Tobias

I must have misunderstood. Thanks for that tip. I’m sure I will use it in the future. On a side note, thanks so much for the frequently asked questions section. I was able to change CSS colors quite easily.

silver melt value chart

Hi,

sure, no problem! ?? You are very welcome!

Best wishes,
Tobias