I need a greater than formula

English
#1

If I wish to have a contest among my sales staff, how do I track the current salesman with the highest sales?

Their sales numbers are totaled up in addresses A, B & C. How do I track If ((A is greater than B & C write “Tom” in address D) and (If B is greater than A & C write “Dick” in address D) and ( if C is greater than A & B write “Harry” in address D) and (if false or all three the same enter “tied” in address D.))

I assume the formula will be written in cell address D.

Simple, Right?

This way I can expand the Calc sheets by breaking out different sales departments and products and the salesmen’s activities by asking the same formula in different addresses.

You can answer here or on my email:

DELETED ADDRESS

Thx. SICH

#3

Welcome!
No, Steven, you shouldn’t leave your email address in plain sight - you don’t want to be inundated with spam, do you? And writing to your personal email is also not very convenient; for a private discussion face to face, here is a special “private messages” mechanism.

You didn’t say what the formula should do if there are two winners (or suddenly all three show the same result). For the case you described, the formula could be
=INDEX($A$1:$C$1;MATCH(MAX($A2:$C2);$A2:$C2;0))
image

#4

See
disask100499decideCompetition.ods (14.0 KB)

And another unsolicited remark:
Competion is the central idea of many sports and games.
It may lead us into a wrong direction otherwise.

#5

Thx for the good advice, but I am a.local politician and book author. I don’t think it possible to get more emails at this point. LOL

#6

Your solution is elegant. I just have to use the label line as an index line to label the sales men and replace the final zero with “Tied” if false.

Unfortunately I tried it exactly as written ($ and all) and I keep getting the lowest result not the highest.

When the result is false (because all are zero) I get a #N/A Message.

My address ranger does not insert $ - and without the $ I keep getting error 511 messages.

Also, my employees are not named - they’re numbered. So I formatted the employee numbers as Text. Does that matter in this formula?

Thx

#7

I’m trying to add a cover page to my sales spreadsheets to summarize at a glance. Giving me an instance employee of the month report card.

Let’s simplify my problem.

Cell A1 is labeled: 001 and
A2 equals 0

Cell B1 is labeled: 010 and
B2 equals 0

Cell C1 is labeled: 100 and
C2 equals 0

Cell D1 is labeled: Winning and
D2 is where I need my answer.

These numbers in Row 2 are constantly changing. This is each employees total from separate spreadsheets keeping track of such weighted aspects as: sales totals, helpfulness, attitude, customer comments, repremands and attaboys, overtime, and etc… Lots of inputs from different supervisors come down to a total on row 2.

I don’t want the numbers - I just want to know which one has more points. This formula would be very helpful…

As these totals in row 2 change I need to determine which is highest and return the employee number label found in row 1 to cell D2.

If all are zeroes I need it to place the default (last months employee of the month: label 101 - I figure if no one has done anything to take it away from last months winner - why change until they do?) to cell D2.

And if two are tied for High Score place the last of the two tied Number labels into D2. This is the higher employee number and provides encouragement to newer employees.

Does that simplify things?

Thx SICH

On another subject:
When is someone going to make a spreadsheet program that uses icons and plain English? Aren’t you tired of having to learn a foreign language and Boolleen mathematics just to track your accounts? I know I am, and I come from the era of DOS: the original (foreign) computer language. Then I was forced to learn Assembler language - Another useless (foreign) language. Then Windows came along… Why can’t we click on a pull down menu, choose the function, click on the addresses, and choose all the available options? Let the program write the Boolleen already…

#8

I don’t know why you can’t, but I can - using the Function Wizard


fnwiz
fnwiz1412×854 46.9 KB

#9

Thank you

I had never heard of it before.

I’ll research it and see if it is truly user friendly.

SICH

Checked it out…

Handy tool. Excellent for debugging: to verify your sentex. But not user friendly for a total novice to Calc/Excel functions and Boolleen. You already need to understand the foreign language before you can translate it… Even with this tool.

What we need is a Star Trek universal translator. Explain in English by clicking on a FAQ and it selects the functions and Boolleen needed for you… Then you just answer a couple questions to enter needed constants, variables, addresses and/or defaults. A calc/Excel program for young children.

After all, they said Windows would never catch on, and that no computer can function without DOS…

Maybe I’ll donate a couple million to Libre to develop such an idea… LOL

SICH

#10

Oh, Mr. Hartley, you are very behind the times - what have you been doing for the last twenty years? The universal translator has existed for a long time. This is called a “programmer”. You can find it here or on some freelancer exchange. And it will cost a lot less than a couple million bucks.
(I’m intentionally formatting this part of the post in italics to indicate that this is off-topic. Text related to the topic of the question will be formatted normally)

The MAX() function will do this for you - just list in parentheses all the cells you want to compare and get the largest value wherever it is located.

Using the MATCH() function, find the position of the maximum in the cells of the second row. At this position, the INDEX() function will return the corresponding name from the first row. It doesn’t matter whether it’s the string “Tom” or the number 100 - that’s the value that will be returned.

I understand that you were confused when it turned out that many people knew the answer to a question that you thought was difficult. And then you tried to hide their embarrassment behind a joke. We would gladly laugh with you, just like 80% of the population of this planet - “yes, really, make it so that we don’t need to think at all!” But, it seems to me, here you will meet representatives of the 20% who take this topic extremely seriously. Sorry, it wasn’t funny

I sincerely hope that you will not try to promote this idea to your electorate. Of course, the idea will be received with enthusiasm. But think about the result, about the future consequences. After two or three generations, people will completely forget how to think; IBM’s slogan is “THINK!” will be forgotten, logical thinking skills will be lost… and not just for a couple of millions, for all the money in the world you will not be able to find a person who can solve your problem.

1 Like
#11

Also OT: Never under-estimate what politicians are capable of doing

1 Like
#12

See, this is what I was talking about.
In 1982 I studied electronics in the Navy, Then in 1987 I started at ASU AND DEVRY to learn computers, programming, and computer design: graduating with a degree in computer engineering. I worked for INTEL ,and later for Orbital Sciences ( a division of NASA). I’m even certified on a CO2 laser to burn and laminate motherboards and I’ve manufactured billions of microchips and CPUs.

Well 40 years later I’m retired, and not on top of every new piece of hardware and new Windows based piece of software.

Now, I have no desire to learn a foreign language Everytime I need a simple chore done.

There MOTO may be THINK, but I think it better to have months of actual productivity over months of setting up the books to track productivity.

I help design the M-ER F-ING computer you use today, only to be treated condensendingly by a Computer Tech - for simply pointing out the obvious: you need to know what Index, Max, Match and a hundred other code words mean and all their special syntextes are before you can use the Wizard.

Great tool for Debugging - but not for a Novice - thank you to whoever pointed it out.

You will know how I feel 40 yrs from now, when your the Tech with a hundred programs no one ever heard of (like DOS, Word Perfect, and DBase), and you have a dozen programming languages no one ever uses anymore (like Assembler) on your resume and then some punk kid is talking to you like youre computer illiterate…

I understand. I guess I had the same feelings you exhibit here when Windows did away with DOS.

To those who are trying to help - Thank you for the information. And to those who feel so smug and superior: you’ll see… Just wait long enough… As AI comes on line there will be no need for techs and programmers anymore. You’ll be like me - a dinosaur with half a dozen degrees in shit that don’t exist anymore.

I guess it is man’s fate to return to university at age 120 - just to learn one more software package - probably to keep my pacemaker operating…

As for all of those people who were lined up to give me the right answer - there was one kind person who offered a solution - and it came back with lots of error messages. But thank you very much for trying.

OH, Wait a minute. You are the fellow who offered a solution - until you got on your high horse - because your solution did not work…

As for expressing the occasional tongue-in-cheek joke. You take yourself way too seriously. And I know people who take themselves too seriously: I’ve known Trump for nearly a decade now…

Don’t worry. I won’t be back.
I’m going to find a community not so full of itself. You’re worst than the Congressmen I work with …

And I’m going to arrange a government grant of 10 million dollars to develop AI in Calc/Excel just to hasten the day that conceded putzes like you won’t be in a position of importance anymore.

SICH

#13

Wait, is this to be understood to mean that you are not demanding a condescendingly? OK! Guys, hold my beer.

By that time, I had already been writing programs for ten years in long-forgotten, dead languages for computers, which can now only be found in museums.
By the way, you forgot to mention that before this you were forced to learn the multiplication tables in a completely incomprehensible foreign language.

You may not take my word for it, but it’s true - most of the gurus here are your age. And also pensioners.

Even the most developed artificial intelligence, after such explanations, would sooner or later be indignant: “You are a two-legged leather bag! Can you really explain what you want and what you can’t do?”

Okay, jokes aside. Can you just show the data and formulas you tried? The task of finding a value in a table is not very difficult, I am sure that with joint efforts we will still create the necessary formula.

It’s a pity that he left… In his search, he may end up in a punk kid gang - they will teach him bad things.

#14

Did anybody check this suggested solution?
Slightly enhanced suggestion:
disask100499decideCompetitionB.ods (13.3 KB)

#15

OT A politician’s promise, I guess. We know what they are worth.

#16

Yes my friend, I looked at this solution. It’s just terrible! Just imagine how much time it would take you to explain the meaning of the RANK() and TEXTJOIN() functions! How many reproaches would you receive for asking to press three Ctrl+Shift+Enter buttons at once? It’s good that he didn’t look at your decision. (To be honest, the formula is excellent, especially in the second file: as always, it’s exhaustive and nothing superfluous)

2 Likes
#17

By the way, I decided to see what the AI had to say on this topic.
That’s what the Bard said

image
image953×638 113 KB

In other words, even if he gives him a bribe of two million, the answer will still be the same: “Use these three functions and don’t you dare say that Index and Match are foreign words!”

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.