Ask Question Forum:
Model Library:2025-02-08:A.I. model is online auto reply
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by duncanb7
at 2024-05-06 01:40:29
ModifyPoint:500 Replies:15 POST_ID:828500USER_ID:11059
Topic:
PHP Scripting Language;Microsoft Excel Spreadsheet Software;Visual Basic Programming
I would like to know how you do faster calculation in php if you have to
do such as simple calculation ,column 4=coumnl1*column2*column3 and then do summation of column4 for 5000 rows.
In excel VBA, I will use rg.offset(0,0).FormulaR1C1="rc2*rc3*rc4", where rg as range from A1 to A5000
something like, it will copy the formula into every cell of 5000 rows in Excel,
and speed is really fast to get the final answer.
How about we do it in php, we just save all column and rows cells into array
then loop all row cells and do basis calculation. Any other method available will
be fastest in php ?
Please advise
Duncan
do such as simple calculation ,column 4=coumnl1*column2*column3 and then do summation of column4 for 5000 rows.
In excel VBA, I will use rg.offset(0,0).FormulaR1C1="rc2*rc3*rc4", where rg as range from A1 to A5000
something like, it will copy the formula into every cell of 5000 rows in Excel,
and speed is really fast to get the final answer.
How about we do it in php, we just save all column and rows cells into array
then loop all row cells and do basis calculation. Any other method available will
be fastest in php ?
Please advise
Duncan
Author: duncanb7 replied at 2024-05-23 23:43:05
Thanks for your reply
Assisted Solution
Expert: Ray Paseur replied at 2024-05-06 12:03:07
72 points EXCELLENT
No. Can you please explain it in plain language? Thanks, ~Ray
Author: duncanb7 replied at 2024-05-06 11:33:54
Do you know it in Excel VBA.
Assisted Solution
Expert: Ray Paseur replied at 2024-05-06 11:12:57
71 points EXCELLENT
Sorry, I do not know what this means:
rg.offset(0.0).FormulaR1C1
rg.offset(0.0).FormulaR1C1
Author: duncanb7 replied at 2024-05-06 10:22:45
it seems it is really good.
Do you think PHP can do everhting as VBA's rg.offset(0.0).FormulaR1C1 ?
If yes that will be prefect
Do you think PHP can do everhting as VBA's rg.offset(0.0).FormulaR1C1 ?
If yes that will be prefect
Assisted Solution
Expert: Ray Paseur replied at 2024-05-06 10:16:27
71 points EXCELLENT
I used a small subset of the data to create and test this proof-of-concept script.
The script is here:
http://www.laprbass.com/RAY_temp_duncanb7.php
The test data is here:
http://www.laprbass.com/RAY_temp_duncanb7.csv
The script is here:
http://www.laprbass.com/RAY_temp_duncanb7.php
The test data is here:
http://www.laprbass.com/RAY_temp_duncanb7.csv
<?php // RAY_temp_duncanb7.phperror_reporting(E_ALL);// DEMONSTRATE HOW TO USE fgetcsv() TO READ A FILE/* DATA LOOKS LIKE THIS:1,2,102,1102,2248082,3,103,1103,6816543,4,104,1104,13777924,5,105,1105,23205005,6,106,1106,35170806,7,107,1107,49748587,8,108,1108,67011848,9,109,1109,87034329,10,110,1110,1098900010,11,111,1111,1356531011,12,112,1112,16439808*/// START WITH ZERO$tot = 0;$fp = fopen('RAY_temp_duncanb7.csv', 'r');while (!feof($fp)){ // GET AN ARRAY $csv = fgetcsv($fp); // REMOVE THE RIGHTMOST COLUMN (E1=A1*B1*C1*D1) array_pop($csv); // MULTIPLY THE CONTENTS OF THE ARRAY $prd = array_product($csv); // SEE THE ARRAYS AND PRODUCTS echo PHP_EOL . '<br/>'; var_dump($csv); echo PHP_EOL . '<br/>'; var_dump($prd); // SUM OF PRODUCTS $tot += $prd; // SEE THE SUM echo PHP_EOL . '<br/>'; var_dump($tot);}echo "<br/><br/>" . PHP_EOL;echo "THE FINAL VALUE IS ";echo number_format($tot); 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47:48:49:50:51:52:
Author: duncanb7 replied at 2024-05-06 09:25:03
Please download attach for csv format
Author: duncanb7 replied at 2024-05-06 09:21:40
Please download the attach data file foror 5000*5 cells.
If you have faster method to calculate, E1=A1*B1*C1*D1 from 1 to 5000 by Php or others
Please let us know it i
Assisted Solution
Expert: Ray Paseur replied at 2024-05-06 07:54:41
71 points EXCELLENT
No, a data conversion to a different format would be a non-value-added part of the exercise. Please show us some of the test data, thanks.
Author: duncanb7 replied at 2024-05-06 07:28:36
Yes, it also use fgetcsv() function, but just want to know beside using arrary, any other method could
be faster since I need to handle one cell by cells for all rows that is time consuming, RIght ?
Could you think if convert csv file into xml or mysql format and do php mysqy would be faster ?
be faster since I need to handle one cell by cells for all rows that is time consuming, RIght ?
Could you think if convert csv file into xml or mysql format and do php mysqy would be faster ?
Assisted Solution
Expert: Ray Paseur replied at 2024-05-06 05:30:11
71 points EXCELLENT
Please show us some of the test data, thanks.
You can probably use fgetcsv() to read the lines of CSV data into an array. You can probably use array_sum() to add up the values in the array. You can keep a running total with the math operator +=
For 5,000 lines of four columns each, optimization is not worth the effort. That is a very tiny amount of data by modern standards.
You can probably use fgetcsv() to read the lines of CSV data into an array. You can probably use array_sum() to add up the values in the array. You can keep a running total with the math operator +=
For 5,000 lines of four columns each, optimization is not worth the effort. That is a very tiny amount of data by modern standards.
Author: duncanb7 replied at 2024-05-06 03:55:06
the I download it from other website in csv format
Assisted Solution
Expert: dmgroom replied at 2024-05-06 03:38:02
72 points EXCELLENT
Duncan
where is the data coming from which you want to perform the calculations on?
where is the data coming from which you want to perform the calculations on?
Author: duncanb7 replied at 2024-05-06 03:24:06
I want to do it in php, in excel I am realy familar with
Accepted Solution
Expert: barry houdini replied at 2024-05-06 02:27:34
72 points EXCELLENT
Hello Duncan,
Sorry don't know php but if you are doing this in Excel it can be achieved with a single formula, i.e. using SUMPRODUCT
=SUMPRODUCT(B1:B5000,C1:C5000,D1:D5000)
regards, barry
Sorry don't know php but if you are doing this in Excel it can be achieved with a single formula, i.e. using SUMPRODUCT
=SUMPRODUCT(B1:B5000,C1:C5000,D1:D5000)
regards, barry
