Formula help needed urgently!

Josdee

New Member
Joined
Feb 19, 2005
Messages
7
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Halebop

Board Regular
Joined
Nov 2, 2004
Messages
83
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):

Code:
=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!
 

Josdee

New Member
Joined
Feb 19, 2005
Messages
7
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

Board Regular
Joined
Nov 2, 2004
Messages
83

ADVERTISEMENT

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

New Member
Joined
Feb 19, 2005
Messages
7
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

Board Regular
Joined
Nov 2, 2004
Messages
83

ADVERTISEMENT

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:

Code:
=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

Board Regular
Joined
Nov 2, 2004
Messages
83
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

New Member
Joined
Feb 19, 2005
Messages
7
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

Board Regular
Joined
Nov 2, 2004
Messages
83
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...

Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top