Ask Your Question
0

CALC: How to lookup a formula instead of a value

asked 2017-04-09 21:21:34 +0100

iperetta gravatar image

updated 2017-04-10 02:27:27 +0100

Hi guys

I'm aware of this: https://ask.libreoffice.org/en/questi...

But I need to lookup the formula in a table like this:

=VLOOKUP(A5,Sheet1.$A$3:$C$183,2,1)

Is there a way of doing something as = FORMULA(VLOOKUP(A5,Sheet1.$A$3:$C$183,2,1)) ?

EDITED:

This piece of information is due to the answer of Lupp (thanks anyway!). In my university, there is a process to you earn points as a professor, but the activities have several different ways of being calculated.

Examples (all fake ones):

  • Give class --- 0.5 point per hour per student (Formula = 0.5 * hour * student)
  • Write a paper --- 150 points (Formula = 150)
  • Being a coordinator --- 30 points per month (Formula = 30 * month)

And I need to select which ones I did in the last period, as well as calculate how many points do I have. I can select either none, one or more times each item (e.g., I could give 3 classes and be coordinator of two different fields).

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-04-10 00:02:31 +0100

Lupp gravatar image

updated 2017-04-10 00:04:20 +0100

You can only use VLOOKUP if the formulas are text content or text results in the first column of the range given as lookup table.

The much more flexible combinations of MATCH with either INDEX or OFFSET you can also use to match against formulas and to get access to a corresponding value with the help of the result. As you need an array of formulas to search for a match of a specific formula given as text, you need to apply MATCH under array evaluation. See this attached example.

However, this is a very strange attempt, and I would really ask you to give a realistic example of a task for whose solution such an approach is useful.

edit flag offensive delete link more

Comments

Thanks, @Lupp, I could figure it out with your help... But now I have another issue with how to apply those formulas in the new spreadsheet...

iperetta gravatar imageiperetta ( 2017-04-10 02:25:32 +0100 )edit
0

answered 2017-04-10 02:24:30 +0100

iperetta gravatar image

updated 2017-04-11 02:27:45 +0100

So, I did a spreadsheet containing all those rules and related formulas. Actually, after the help of Lupp I was able to make this:

=MID(FORMULA(INDIRECT("Sheet1.G"&MATCH(A5,Sheet1.$A$1:$A$183,1))),2,999)

and I'm able to do these in another spreadsheet:

  • Give class [course 1] (0.5 * hour * student)
  • Give class [course 2] (0.5 * hour * student)
  • Give class [course 3] (0.5 * hour * student)
  • Being a coordinator [lab 1] (30 * month)
  • Being a coordinator [trainees] (30 * month)

Now I'm stuck in how to use those "text formulas" to apply to the new spreadsheet... :-(

EDITED:

Here is a "minimal" example: example.ods

edit flag offensive delete link more

Comments

I would try to help you further if I understood your point. Please make accessible a relevant example (depersonalized // enough specimen to fight misunderstandings likely if given too few examples) of what you have and the results you try to achieve.

Anyway it still seems extremely strange to me to lookup for formulae. I still don't know what A5 (e.g.), Sheet1.$A$1..., and Sheet1.G...somerange actually contain. In what Sheet.Cell(s) does your new formula work?

Lupp gravatar imageLupp ( 2017-04-10 12:15:11 +0100 )edit

Hi @Lupp. I've just included an .ODS with a minimal example. If you could help me, I would appreciate very much. If you could post the full solution, I'll give you the answer.

iperetta gravatar imageiperetta ( 2017-04-11 02:29:18 +0100 )edit

Ok. I am interested due to an "academic" interest: Paradigmatic Q.
You will know yourself best that you are fighting "The slings and arrows of outrageous fortune bureaucracy...". As long as there isn't any restriction to the expressions for the contributions to your points-account this will never finally succeed. As soon as bureaucracy invents a new variant (using two thresholds, e.g. instead of one at maximum) you will have to not just enter new criteria values, but new formulae, too.

Lupp gravatar imageLupp ( 2017-04-11 10:52:35 +0100 )edit

Since Calc does not provide an EVALUATEFORMULA function this cannot be done by adding the formula itself as a text into a parametrising cell. The way you try to cope with the problem is nonetheless problematic, imo. (I will be back later if I find some more spare time.)

Lupp gravatar imageLupp ( 2017-04-11 10:58:03 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-04-09 21:21:34 +0100

Seen: 200 times

Last updated: Apr 11 '17