Ratios Formula 1

[SQLScholar]

Hey all,

If i have 2 values... either of which could be bigger, either of which could be 0. I want to do something like this.

If both values are 0, then put 0:0.

If value 1 is 0 put 0:value2

If value 2 is 0 put value1:0

If then value 1 is greater then put value1/value2:1
If then value 2 is greater then put 1:value1/value2

This is where i am stuck. Currently this all includes rounding to 0 DP. I want to have all this, but also if the value used in the non written bit of the equation is less then 2 put the rounding to 1 DP.

I dont want everything rounded... but a few 1:1`s when there shouldnt be look stupid. Current code below

=IF(AND(C7="",C33<>""),"0:"&C33,IF(C33="","",IF(C33=0,C7&":0",IF(C7/C33<1,"1:"&ROUND(C33/C7,0),ROUND(C7/C33,0)&":1"))))

sorry for being a tad complex.

TIA

Dan



----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SkipVought]

Dan,

Not really sure what you are asking.

"I dont want everything rounded" ???
[tt]
=IF(A1=0,IF(B1=0,"0:0","0:"&B1),IF(B1=0,A1&":0",IF(A1>B1,A1/B1&":1","1:"&B1/A1)))
[/tt]


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[GlennUK]

You could control the rounding of each part, ( i'm guessing here ), so here's my attempt at what i think you want :

=ROUND(IF(val1=0,0,IF(val2=0,val1,val1/IF(val2>val1,val1,val2))),IF(val1<=val2,0,1))&":"&ROUND(IF(val2=0,0,IF(val1=0,val2,val1/IF(val1>val2,val1,val2))),IF(val1>=val2,0,1))

I've named the 2 cells val1 and val2 for readability.



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[SQLScholar]

I tried your formula.. didnt seem to round at the correct times

I will try and explain.

If i do =round(val1/val2,0)&":1" if it goes under 1.5 it looks rather stupid... either giving 1:1 when it strictly isnt, or 0:1 which it isnt either. So the first thing would be to cope with this, with swapping the :1 to a 1: and having it round the other way (so figure not below 1).

Then if the figure is below 1.5 to round to 1 DP rather then 0. So that i dont get val1=7 and val2=9 showing 1:1. it would show something like 1.4:1

Any help GREATLY appreciated,

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[GlennUK]

Dan,

are you trying my formula ... it does exactly what you ask.



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[SQLScholar]

I did try it as i said but it doesnt work....

I for instance i have an 8.3:1 which proves it is not working. Thanks anyway.

I think this might be getting a tad over complicated. So i have reverted back to

=IF(AND(C7="",C33<>""),"0:"&C33,IF(C33="","",IF(C33=0,C7&":0",IF(C7/C33<1.5,ROUND(C7/C33,1)&":1",ROUND(C7/C33,0)&":1"))))

Which does everything barring the below 0 switch to 1:

Thanks anyways

STAR

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[GlennUK]

Thanks, but, to quote your original request :

If then value 1 is greater then put value1/value2:1

How am I supposed to take that another way? For a value1 of 58 and a value2 of 7 that gives 8.3:1????

Ah well, time to go home anyway.

Glad you found something that suits you.





Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[SQLScholar]

because my original post said

"Currently this all includes rounding to 0 DP. I want to have all this, but also if the value used in the non written bit of the equation is less then 2 put the rounding to 1 DP"

As far as my logic goes 8 is not less the 2 so it should be to 0dp.. not 1dp

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[GlennUK]

Ah, that explains it. I didn't understand what the "non-written bit of the equation" was referring to.

This version takes care of that :

=ROUND(IF(val1=0,0,IF(val2=0,val1,val1/IF(val2>val1,val1,val2))),IF(val1<=val2,0,IF(val1/val2>2,0,1)))&":"&ROUND(IF(val2=0,0,IF(val1=0,val2,val1/IF(val1>val2,val1,val2))),IF(val1>=val2,0,1))

Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.