Enter the highest value

Discuss the spreadsheet application

Re: Enter the highest value

Postby sandromerlino » Wed Aug 08, 2018 2:23 pm

Dear keme
I would like to tell you that i entered your formula in the cell F107 but the result I have is different from the result of my formulas

not finished the reply yet give 5 10 minutes
Last edited by sandromerlino on Wed Aug 08, 2018 2:37 pm, edited 1 time in total.
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby RoryOF » Wed Aug 08, 2018 2:28 pm

Check that all your values are entered as the correct type: use /View /Value highlighting
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 26942
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Enter the highest value

Postby sandromerlino » Wed Aug 08, 2018 2:46 pm

Dear keme,

I would like to tell you that i entered your formula in the cell F107 but the result I have is different from the result of my formulas in D118 and E118 and consequently in F118 and G118
More over in my formulas i did not consider that my boss does not want to see error message and i forgot to include that when the denominator is 0 the result has to be 0
Please could you check for it? I am not able to do these 2 things. They are too difficult

I entered the same file named "scelta del valore- formula 01" in my drop box
link:https://www.dropbox.com/s/n1qtsqljos9btyi/scelta%20del%20valore-%20formula%2001.ods?dl=0

Thank you very much
sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby keme » Wed Aug 08, 2018 3:46 pm

There are a few flaws in the file, which make it cumbersome to work with.

One that I have already mentioned: source data is split up by month. I believe my previous OFFSET() trick solved that, but I am not 100% certain.

Another one: you consistently work with dates as text, instead of using proper date values and use formatting to display the desired portions. Using the TEXT() function with Italian format codes does not work with my Norwegian setup. ("Giorno" = "Dag", so where you use "ggg" to format the date, I need "ddd"). I have set "spreadsheet locale" to italian, which seems to have rectified some of the problems.

There still is some inconsistent behavior which I haven't determined the cause of.

This does not make it impossible to help you, it just takes longer to do it. I am telling this just to reassure you that I am working on it, even though you don't hear a lot from me.
User avatar
keme
Volunteer
 
Posts: 2905
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Enter the highest value

Postby sandromerlino » Wed Aug 08, 2018 7:35 pm

Dear keme,
Thank you very much for your help
Now i know that you are trying to find the formula I'll wait for your.

Thanks again

sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby keme » Thu Aug 09, 2018 8:54 am

I think I got it now.

My monster formula for risultato.F107 should have been:
Code: Select all   Expand viewCollapse view
=MAX(OFFSET(D.N4;100*MONTH(TODAY());0)/SUMPRODUCT(OFFSET(T.J25;100*MONTH(TODAY());0;DAYSINMONTH(TODAY());1)<>"chiuso";OFFSET(T.J25;100*MONTH(TODAY());0;DAYSINMONTH(TODAY());1)<>"close");(OFFSET(D.N4;100*MONTH(TODAY());0)-SUMPRODUCT(OFFSET(D.H25;100*MONTH(TODAY());0;DAYSINMONTH(TODAY());1);OFFSET(D.E25;100*MONTH(TODAY());0;DAYSINMONTH(TODAY());1)<TODAY()))/SUMPRODUCT(OFFSET(T.J25;100*MONTH(TODAY());0;DAYSINMONTH(TODAY());1)<>"chiuso";OFFSET(T.J25;100*MONTH(TODAY());0;DAYSINMONTH/TODAY());1)<>"close";OFFSET(T.F25;100*MONTH(TODAY();0;DAYSINMONTH(TODAY());1)>=TODAY()))
... which translates to ...
Code: Select all   Expand viewCollapse view
=MAX(SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI()))
I haven't tested thoroughly. The formula may return an error at the turn of a month, but I don't think so.

You could also use your existing set of formulas, and insert this in F107:
Code: Select all   Expand viewCollapse view
=SUM(G111:G122)

A bit cleaner, don't you think? ;)
This makes use of the fact that boolean FALSE is represented by the number 0. An IF() without the third parameter returns FALSE when the condition is not true.

Hide rows 109-123 if you want a cleaner look.

I haven't looked at the other formula (in I107), but I guess you can use the same strategy there, with calculation per month, a conditional column and a sum.
User avatar
keme
Volunteer
 
Posts: 2905
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Enter the highest value

Postby sandromerlino » Fri Aug 10, 2018 12:58 pm

Dear keme
I have tested your formula and the result is much better. Thank you so much.
I noticed: example
Considering today.
If you enter close or chiuso from today till the and of the month because restructuring the store.
The formula because the dominator is 0 it gives me #div!.My boss would like 0
Could you please check if it is possible?

Thank you

sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby keme » Fri Aug 10, 2018 3:25 pm

The clean and simple solution:
Keep the calculation with the error in F107. Hide row 107.

In F106, enter:
Code: Select all   Expand viewCollapse view
=IF(ISERROR(F107);0;F107)
... or ...
Code: Select all   Expand viewCollapse view
=SE(VAL.ERRORE(F107);0;F107)

If you must, substitute the existing formula for F107 (yes, twice) in the formula above. Impossible to read, but it will work.

And for integrity's sake, a few points to consider:
  • Is there a possibility that other conditions return value zero?
  • What happens if the close/chiuso indicator is mistyped? Will it ever be mistyped?
  • Can you reuse this for leap years?
User avatar
keme
Volunteer
 
Posts: 2905
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Enter the highest value

Postby sandromerlino » Fri Aug 10, 2018 10:17 pm

Dear keme,
Unfortunately I cannot solve the problem as you said in the simplest way. My boss wants formula big or small in a cell like the one you did (which is fantastic)
without no hidden cells. I cannot use these procedures.
The clean and simple solution:
Keep the calculation with the error in F107. Hide row 107.
In F106, enter:
CODE: SELECT ALL EXPAND VIEW
=IF(ISERROR(F107);0;F107)... or ...
CODE: SELECT ALL EXPAND VIEW
=SE(VAL.ERRORE(F107);0;F107)

Your question:
Is there a possibility that other conditions return value zero?
-> the value 0 can be given when you open a store and you do not sell any item
Your question:
What happens if the close/chiuso indicator is mistyped? Will it ever be mistyped?
-> Luckyly cannot be mistyped
Your question:
Can you reuse this for leap years?
->I have already a formula which calculates if an year is leap year or not and my boss is happy with it

What i would like to ask you, hoping to not bother you to much is if you can enter (if it is possible) this part(=SE(VAL.ERRORE(denominator);0;the formula you wrote))
I hope i am clear. In words i would like to say that if the denominator is 0 than 0 if not i need to execute your fantastic formula. I cannot do it myself otherwise i would evoid you another problem. I hope there are not caracter leght problems in the cell.

Thank you again

sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby keme » Fri Aug 10, 2018 11:41 pm

The questions listed were intended more as hints toward possible mistakes or misunderstandings, not so much as requirement to respond. Looks like you have it covered.

The error caught by SE.ERRORE is caused by zero denominator, so if you want to simplify you should check for denominator=0, not SE.ERRORE(denominator). There are two different denominators which both need checking, so I am not sure that the end result is much simpler. Try it and see.
User avatar
keme
Volunteer
 
Posts: 2905
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Enter the highest value

Postby Bald Eagle » Sat Aug 11, 2018 3:05 am

In your calculations, you try to do: DATE(L120;1;1)
I don't think that's valid.
You're trying to use the date() function on a text object that's just a day.

The docs say to use today() or now() in the weekday() function.
I replaced that and it works.

I did

[E105]
Code: Select all   Expand viewCollapse view
=IF(Q5="Italiano";1;0)


[E106]
Code: Select all   Expand viewCollapse view
=OFFSET (D109;E105;0)


[D109] =
Code: Select all   Expand viewCollapse view
ddd

[D110] =
Code: Select all   Expand viewCollapse view
ggg


and then replaced "ggg" in the text() function with E106

Code: Select all   Expand viewCollapse view
=IF(Q5="inglese";INDEX({"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"};WEEKDAY(TODAY();2));TEXT(TODAY();E106))


That way you can switch between the function syntaxes as the language changes.

With regard to your denominator, can you simply use max([denominator];1) ?
I don't actually know where that calculation is in the spreadsheet.

Perhaps consider creating a "scratchpad" sheet, that has various values and ranges of values that don't need to be seen, and then reference those cells or name them. It might make for a neater workbook and easier to read formulas.
OpenOffice 4.1.1 on Windows 7
Bald Eagle
 
Posts: 38
Joined: Wed Apr 19, 2017 9:22 pm

Re: Enter the highest value

Postby sandromerlino » Sat Aug 11, 2018 9:46 am

Dear keme
Thank you for yor reply i did not understand that you did not want a reply for the listed questions.
You are right and i'll try to enter this part denominator=0 in your formula for the 2 deminators.
I see it very difficult to succeed in this hard work but i'll do my best. Unfortunately i'll go by attempts not knowledge.
If you have time to help me in this I would appreciate it very much i would feel more sure about the risoult

Thank you
sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby Villeroy » Sat Aug 11, 2018 9:49 pm

sandromerlino wrote: Unfortunately i'll go by attempts not knowledge.

Stop this attitude. The document you uploaded is useless crap.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25570
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Enter the highest value

Postby sandromerlino » Sat Aug 11, 2018 10:52 pm

Dear keme
I would like to ask you if you can help me to enter in your formula:
Code: Select all   Expand viewCollapse view
=MAX(SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI()))


The denominator controls:
for the first denominator:
Code: Select all   Expand viewCollapse view
=MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close")

for the second denominator:
Code: Select all   Expand viewCollapse view
=MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI())


In order to have this total formula that should be the final one:
=max(if(control first denominar=0;0;formula);if(control second denominator=0;0;formula))

I have been trying all day but not any result but err:510. I hope it can stay in a cell or maximum 2

I think after it we can enter SOLVED

Thank you so much

sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby Bald Eagle » Sun Aug 12, 2018 4:19 pm

Villeroy wrote: Stop this attitude. The document you uploaded is useless crap.


lol. That's one way to put it.

Blindly groping truly is a bad strategy for success.
A sequential, progressive, building up of a complex formula, using a series of "helper" columns, with a few SMALL test examples would be the way to go about determining a solution to whatever they're trying to do, as well as showing where and why the errors arise.

The document they uploaded is --- strange. I have a huge monster of a spreadsheet at work - with many examples of bad, horrific spreadsheet-wannabe-database misuse, pushing 3000 rows of data - and it loads and processes better than this.
I made some changes and saved it - and OO seems to have trouble even saving it. I mean, it's SLOOOOOOOW.

So there is indeed something odd about the spreadsheet file itself.
OpenOffice 4.1.1 on Windows 7
Bald Eagle
 
Posts: 38
Joined: Wed Apr 19, 2017 9:22 pm

Re: Enter the highest value

Postby sandromerlino » Sun Aug 12, 2018 9:58 pm

Dear all,
I am writing this post to learn from you some things as i did to use the dropbox, share the file i attach in it and share the link.(thanks to keme)
First of all there is a rule in life and is that if you want help, you do not have to disappoint the people that should help you. Is it not right?
Thalking of my file:
The first problem i had was to attach the file. It is a havy and long file but i need the 3 sheets, for the calculation i cannot avoid any.
The second is that i am not english mother tongue so it is not easy to explain it.
The third is that i have some formulas in the file of course in italian that explain what i want to do in the final formula. I traslated them in english but something strange
happened in the sense that when i reopened the file they were again in italian. My problem than is i do not know how you see my file when you open it from the drop box.
The only way is that you tell me and what i can do about it for you to have the file in the way you need.

A positive point is that keme succeded in making 95% of the right formula. I forgot to tell him that my boss never wants as a result of the formula #div!0.(he wants 0)
This is possible to avoid, of course you know better than me, if we control the denominators of the formula.
How was he able to understand the problem? Did he see my file?, which of the 3?......

If you want to help me at this point you do not have to understand the problem just enter the control for the denominatrs in the formula below but i am here to
summarise the problem or do anything you need.

Code: Select all   Expand viewCollapse view
=MAX(SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI()))


I have more problems so i would like to be part of this forum in the future. Please give me as much advise as possible

Thank you ery much

sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby keme » Mon Aug 13, 2018 11:49 am

Some advice
Spreadsheet best practice: Split complex tasks into smaller subtasks.
Forum best practice: Describe your purpose, not just a small part of it. Explain what you have done, and what problems you experience, what you see and what you want to see.
You have already explained that your boss would not accept your solution with one formula for each month and a separate summary formula. Everything had to be in one formula. So, I took one of your formulas, substituted the static references with dynamic ones using OFFSET(). My one-liner solution works as requested, but is more vulnerable to sheet changes than your original and more difficult to debug.

Pasting the monster formula twice into my suggestion for error checking formula yields next order of magnitude monster and eliminates the DIV/0 error:
Code: Select all   Expand viewCollapse view
=SE(VAL.ERRORE(MAX(SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI())));0;MAX(SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI())))

I tried that, and it worked. I won't bother to post the English version, because it is of no interest to anyone.

Your suggestion to rather test for zero divisor should also work. Pasting the required sections of the original formula into your structure =max(if(control first denominar=0;0;formula);if(control second denominator=0;0;formula)) should yield:
Code: Select all   Expand viewCollapse view
=MAX(SE(MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close")=0;0;SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso");SE(MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI())));0;MAX(SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)=0;0;SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI()))))))

I haven't tested this. If it doesn't work, then so be it. It may be my fault (perhaps I made mistakes in copying), or it may be that Calc does not support formulas of this magnitude.

These formulas are possible to build from stepwise development, but it is better to keep the steps in separate cells. The formulas above are near impossible to maintain. If your boss does not want to see the error indicators, it is better to hide them than to eliminate them.
User avatar
keme
Volunteer
 
Posts: 2905
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Enter the highest value

Postby robleyd » Mon Aug 13, 2018 11:57 am

Keme - that is impressive and daunting.
I pity the person who inherits this spreadsheet and has to try and understand what it is doing.
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1849
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Enter the highest value

Postby sandromerlino » Tue Aug 14, 2018 10:14 pm

Dear keme,
Thank you very much for the final formula.
I was very happy to see it so I copied it immediately to my file and just after the paste it happend what i was scares. It is too long and it exceed the number of characters in a cell (i mentioned it in some of my previous posts). I use Openoffice 4.1.4
I have been trying to split it in 2 formulas but no seccess. I'll keep doing it in the next days and if i am not able i'll ask you to do it for me when you have time.
I would like to put solved as soon as possible

I promise that my next post will be split in steps, with examples and i agree that other people has to learn something

Thank you

sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby sandromerlino » Wed Aug 15, 2018 11:06 pm

Dear keme,
I hope you are so patient to split your final formula in 2 (hoping that they do not give me error as a result due to my OpenOffice version)
I have tried many times to avoid you this further job in these days but "NO GOOD RESULT"

Thank you

sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Re: Enter the highest value

Postby keme » Wed Aug 15, 2018 11:54 pm

Can you not use the first formula in that post, which tests for error condition instead of testing for the cause of the error condition. It is a bit shorter, and seems to work the way you requested.

If not, I am sure that you can copy the formula parts yourself. I am currently posting from my phone, where copy/paste is a bit of a hassle. Also I think you should do the work yourself. You are more familiar with the original, which reduces the risk of mistakes. You might even learn something from it ;)
User avatar
keme
Volunteer
 
Posts: 2905
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Enter the highest value

Postby sandromerlino » Thu Aug 16, 2018 9:51 pm

Dear keme
After the whole day spent trying to find the way to split the final formula in 2 I'll give you my solution that doesn't work. I'll be glad to know my mistakes
I wanted to split it where the second max formula starts.I Considered to use the 2 cell E103 and F103
In E103 I entered
Code: Select all   Expand viewCollapse view
=MAX(SE(MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close")=0;0;SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso");SE(MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI())));0;F103)

the result is Err:504

In F103 I entered
Code: Select all   Expand viewCollapse view
=((((MAX(SCARTO(D.N4;100*MESE(OGGI());0)/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close");(SCARTO(D.N4;100*MESE(OGGI());0)=0~0~SCARTO(D.N4;100*MESE(OGGI());0)-MATR.SOMMA.PRODOTTO(SCARTO(D.H25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1);SCARTO(D.E25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<OGGI()))/MATR.SOMMA.PRODOTTO(SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"chiuso";SCARTO(T.J25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)<>"close";SCARTO(T.F25;100*MESE(OGGI());0;GIORNI.MESE(OGGI());1)>=OGGI()))))))

the result is Err:502

Take your time if you want to help me
Thank you
sandro
openoffice 3.2 - vista
sandromerlino
 
Posts: 47
Joined: Sat Feb 18, 2012 11:52 pm

Previous

Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 27 guests