We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

formula changed by value in another cell? [closed]

asked 2020-01-04 17:22:23 +0200

DougKMiles gravatar image

updated 2021-06-05 01:01:54 +0200

Alex Kemp gravatar image

Is there a way to set up a formula that changes based on the value in another cell?

I have a summation cell in column G, but I want the value to be calculated differently based on the symbol in column B. Right now I'm doing it with a conditional IF formula, but that only gives me two options. Since I want to use 5 or 6 different symbols appearing in column B, I'm looking for a way to automate that calculation which is different for each symbol.

Many thanks for any suggestions or guidance. image description

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-06-05 01:02:05.828854

1 Answer

Sort by » oldest newest most voted

answered 2020-01-04 17:40:24 +0200

Opaque gravatar image

updated 2020-01-04 17:56:01 +0200


take a look at IFS() function here: LibreOffice Help - IFS, which is designed to avoid nesting of IF functions, which would be quite confusing on a 6-case distinction of your use case scenario.

Meta formula for your case: =IF(ISBLANK(C5);"";IFS(B5="ES";F5*50;B5="MES";F5*4;B5=<NEXTC1>;<NEXTFORM1>....))

Hope that helps.

If the answer helped you to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more


I think that first IF can also be merged into IFS...

Mike Kaganski gravatar imageMike Kaganski ( 2020-01-04 17:55:45 +0200 )edit

@Mike Kaganski - of course =IFS(ISBLANK(C5);"";B5="ES";F5*50;B5="MES";F5*4;B5=<NEXTC1>;<NEXTFORM1>....) should be the same (did not test; I'm too lazy now to typewrite the image data ;-))

Opaque gravatar imageOpaque ( 2020-01-04 17:57:16 +0200 )edit

That's it! Many thanks!

DougKMiles gravatar imageDougKMiles ( 2020-01-04 19:27:35 +0200 )edit

Question Tools

1 follower


Asked: 2020-01-04 17:22:23 +0200

Seen: 123 times

Last updated: Jan 04 '20