Formula help needed urgently!

[Josdee]

Hi, any help on this one will be really appreciated!!!

I am trying to create an “if” formula with multiple arguments. I keep getting #NAME? and #VALUE! Errors. The logic of what I’m trying to input is as follows:

IF B57*B27>=B46 and B63<B57 then B72 applies, however if B63>B57 then {B72*{(( B57*B27)-B46)/(((B63*B27)- (B57*B27))+(( B57*B27)-B46))}-B75-B76

Where:
B27 = 15
B46 = 16 (variable)
B57 = 3
B63 = 3 (variable)
B72 = 188.69 (variable)
B75 & B76 will only have a value if B63>B57

I’ve already spent hours on this one please HELP!!!

Thanks
Dee

 

[Halebop]

Hi Josdee,

Your question is not very clear but I'm having a stab at it...

I had a bit of a challenge because the formula displays differently in the forum to when I use the quote function. It seems the forum applies some scripting functions or something similar to your text and the output is subverted. Use the "Code" function around your example formula to avoid having this occur.

You say b75/B76 will only have a value if it meets a certain criteria so I assume these are supposed to contain your formula? However your formula contains references to these same cells - this seems to be a circular reference?

Assuming the forumla doesn't belong in b75/b76 I think it should look something like this (although I hasten to add I have no idea what it is supposed to do so can't comment if it is achieving what you want):

=IF(AND(B57*B27>=B46,B63<B57),B72,IF(B63>B57,(B72*(((B57*B27)-B46)/(((B63*B27)-(B57*B27))+((B57*B27)-B46)))-B75-B76),
0))

There is a further problem with the formula though. Your 1st nested condition specifies b63 be less than b57. In the 2nd set of conditions you say b63 should be greater than b57. What should it do if it equals? Also, if all these conditions prove false, I assume the output is Nil. You would have to change the "Zero" at the very end if this is not the case.

I'm not sure if any of this helps!

 

[Ed B. Delen]

PLEASE CLEAR UP WHAT YOU MEAN

 

[Josdee]

Thanks Roger.

I see an error in my original request though "IF B57*B27>=B46 and B63B57" Should read "IF B57*B27>=B46 and B63<=B57"

I've made this change in the formula you suggested but excel still comes up with an error in the formula.

Thanks for your help!
Dee

 

[Halebop]

Hi Josdee,

Can you tell me what cell(s) you are putting the formula in? My example works for me so I have assumed something different from what you are actually doing.

 

[Josdee]

Hi Roger,

Regarding your query on cells B75 and B76. The formula is in B77 and b75 & b76 would only have a value if certain criteria contained in their specific formulas are met otherwise they equal zero.

 

[Halebop]

Hmmm. A bit of a mystery Josdee.

Here's an updated version including your re-specified "=" assumption. I do not get an error with either version however, just different outcomes. Again i can't confirm this formula gives the answer you need but it definately has correct syntax and so should not generate an error message.

I'd suggest delete the contents of B77 so it is clear. Once it is clear, re-copy the formula below and paste it into B77:

=IF(AND(B57*B27>=B46,B63<=B57),B72,IF(B63>B57,(B72*(((B57*B27)-B46)/(((B63*B27)-(B57*B27))+((B57*B27)-B46)))-B75-B76),0))

 

[Halebop]

Actually I should have said copy it into the Formula Bar of that cell rather than the cell itself. You'll reformat your cell if you copy directly into it.

 

[Josdee]

Hi Roger,

Absolutely marvelous!!!!!
It is working.....however if I increase B63=4 and B46=50 I get a negative value. How can I prevent the solution from going below zero.

Your help has been brilliant!
Thanks
Dee

 

[Halebop]

You're Welcome Josdee.

It's after 3am here in New Zealand so I'm not sure how great my cognitive functions are right now. I'm forlornly waiting for someone to notice my request for help!

Anyway. This isn't the most technical solution but it is elegant in its simplicity. Assuming B77 is calculating everything as you desire but you just don't want to have a score less than 0 the easiest solution is to leave the formula alone and use a new cell. In this case I personally would hide b77 if possible because it won't be serving a useful purpose staying visible and use b78 for the following...

=if(b77<0,0,b77)

This just tests to see if b77 is less than 0. If it is, it changes the score to 0, if not, it displays the higher value in b77.