Ask Your Question
0

'IF' formula not working

asked 2016-01-15 11:06:53 +0200

random32 gravatar image

The following formula is not returning any result in LibreOffice Calc. In Excel 2013, the result is 33,525

Following is the formula:

IF(2500000<=B10>=1000000,1.2B27,IF(5000000<=B10>=2500000,1.3B27,IF(7500000<=B10>=5000000,1.5B27,IF(10000000<=B101>=7500000,1.5B27,IF(15000000<=B10>10000000,2,IF(20000000<=B10>=15000000,2.2B27,IF(25000000<=B10>=20000000,2.5B27,IF(30000000<=B10>=25000000,2.7B27,IF(35000000<=B10>=30000000,2.8B27,IF(40000000<=B10>=2.9B27,IF(50000000<=B10>=40000000,3B27,1)))))))))))

edit retag flag offensive close merge delete

Comments

OK, let's look at this formula together ... Is that this piece - ...10000000<=B101>=7500000,... - was written correctly? In other parts you check the value of B10, but not of B101 Next questionable fragment - ...40000000<=B10>=2.9B27... Everywhere you compare with a constant, but here with a calculated expression...

JohnSUN gravatar imageJohnSUN ( 2016-01-15 12:41:47 +0200 )edit

Tell us about these two pieces, and then we will tell you how to write the "B10 between the Value and Another Value"

JohnSUN gravatar imageJohnSUN ( 2016-01-15 12:42:03 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2016-01-15 14:41:54 +0200

Lupp gravatar image

updated 2016-01-15 14:45:46 +0200

Sorry! The formula is not acceptable syntactically. In addition it cannot be expected to produce something reasonable, supposed some syntactical rectification is made. Then, you tell us:

The following formula is not returning any result in LibreOffice Calc.

Of course the formula must be recognised as one. Therefore it has to begin with = This assured it will return something in any case. Even if the formula is syntactically unacceptable you will get an alert explaining this. Otherwise, in general, the result may be number, text or error value by type. Do not tell us what did not happen (as compared with your expectations) but what actually happened, and what you expected in addition, if applicable. Reduce your examples to be relevant but simple, please. More than two nested calls of IF are mostly bad in the first place. They are surely not needed to give an example of your issue.

Let me focus now on the very first condition in your example reading 2500000<=B10>=1000000:
1) Extended comparisons (inequations by mathematical terms) are not supported by Calc.
2) Mathematical notation allowing for extended inequations will demand the same direction for each step.
3) As the evaluator of Calc is specified, it will first decide 2500000<=B10 which may result in FALSE numerically equivalent to 0 or TRUE numerically equivalent to 1. The result will then be compared by >= with 1000000. Since 0 and 1 both are less than 1000000 the condition will always be FALSE.

And so on.

What do you expext 2.9B27 to be taken for? It should not be an acceptable expression in any spreadsheet.

edit flag offensive delete link more

Comments

IMHO 2.9B27 meant 2.9 * B27. And fourth 1.5B27 meant 1.7 * B27 or 1.8 * B27

JohnSUN gravatar imageJohnSUN ( 2016-01-15 14:50:32 +0200 )edit

@JohnSUN : I guessed similarly, but do not understand your remark
And fourth 1.5B27 meant 1.7 * B27 or 1.8 * B27.
The subexpressions mentioned should not be accepted by Excel, as the OP claimed, anyway.

Lupp gravatar imageLupp ( 2016-01-15 14:55:12 +0200 )edit

random32 has written 1.5B27 twice - on [5000000..7500000] and on [7500000..10000000]. I think it's just a typo

JohnSUN gravatar imageJohnSUN ( 2016-01-15 15:36:16 +0200 )edit
0

answered 2016-01-15 14:09:21 +0200

JohnSUN gravatar image

updated 2016-01-15 19:15:06 +0200

I think you mean something like this

=B27 * LOOKUP(B10/100000, {0; 10; 25; 50; 75; 100; 150; 200; 250; 300; 350; 400; 500.00001}, {0; 1.2; 1.3; 1.5; 1.7; 2; 2.2; 2.5; 2.7; 2.8; 2.9; 3; 0})

Also, a very similar result you can get with this formula

=B27*ROUND(1,111504005822E-023*B10^3-1,81127850644E-015*B10^2+0,000000102675054609*B10+1,066893558;1)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-01-15 11:06:53 +0200

Seen: 192 times

Last updated: Jan 15 '16