How to show value which is based on multiple conditions?

General discussion on OpenOffice Calc and its spreadsheets.

How to show value which is based on multiple conditions?

Postby UniSET » Fri Jun 13, 2014 7:28 am

Hi,

I need a formula that would do the following: If the value in cell X is Y, show Z. I have about 80 or so relationships between Y and Z.
Z is a percentage, but not one that is calculated so it is text, and Y is never an integer.
So, for example, let's say the value in cell X is 8.9. Anything equal to or greater than 8 but less than 9 needs to show 67%. Anything equal to or greater than 7 but less than 9 needs to show 65%. And so on and so on. How do I do this?

Thanks!
UniSET
 
Posts: 22
Joined: Tue Mar 25, 2014 6:33 am

Re: How to show value which is based on multiple conditions?

Postby Elwood M. Walker » Fri Jun 13, 2014 10:14 am

Hi,

The Max(Range) function returns the largest value in Range. The Large(Range;n) function returns the nth largest value in Range. If your values are in the cells A1:A100, then the largest minus second largest value would be given by
Code: Select all
=MAX(A1:A100)-LARGE(A1:A100;2)


Thanks.
Elwood M. Walker
 
Posts: 33
Joined: Mon Mar 24, 2014 6:09 am

Re: How to show value which is based on multiple conditions?

Postby Bryan L. Warfield » Fri Jun 13, 2014 10:17 am

hello,

Name your styles Style1 to Style7
=VLOOKUP(K3;$AI$3:$AJ$103;2;0)+STYLE("Style"&MATCH(MAX($B$3:$H$3);$B$3:$H$3;0))
STYLE returns zero. Adding zero does not affect the main operation which is the vlookup.
Then we concatenate the style name prefix "Style" with the position number where MAX$B$3:$H$3) is found within $B$3:$H$3 so the concatenation gives "Style3" if the 3rd cell D3 is the biggest number.

Thanks,
Bryan L. Warfield
 
Posts: 29
Joined: Mon Mar 24, 2014 6:11 am


Return to OpenOffice.org Calc

Who is online

Users browsing this forum: No registered users and 0 guests

cron