If formula problem

[Phoenix]

I need the formula to do the following:
if i mark an "x" in E18 if i sell motorbikes today, and the total wil
automatically be C18*D18*E9=total. If i buy motorbikes today i need th
same operation, but the total will be in minus(C18*D18*E10=-total).
need a formula that covers all the "x" (for today and tomorrow), bu
always just one "X" in the same line

I used something like this formula before, with "index" and "match
operations.

Any suggestions guys?


Phoeni

Attachment filename: if formula.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46508

 

[Bob Phillips]

Hi Phoenix,

How about

=C18*D18*(OR(E18="x",F18="x"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Phoenix]

Not quite....cause the "x" refers to the factors over. when we make a
"x" for example in E18, the result will be C18*D18*E9. That is if w
sell. When buying the result will the factor for selling.

Phoeni

 

[Bob Phillips]

That's not what is says on the website.

The thing I don't get is how to know if it is buy or sell, if it is cars or
bikes, what data item says that. The basic formula just needs the factor
adding

=C18*D18*(OR(E18="x",F18="x"))*E9 (or is it E8, F8, F9, E10, F10, E11, F10,
E11, F11, E12 or F12?).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

Think you'd need additional inputs / specs
for the Vehicle Type [bike or car]
and whether it is a Buy or Sell

Try this additional set-up in Sheet1

Put in H9:H12
: MBS, MBB, MCS, MCB

Put the labels
in A17: Vehicle Type
in B17: Buy / Sell

In A18:A20 will be the inputs / specs: MB or MC
[where MB = Motor Bike, MC = Motor Car]

In B18:B20 will be the inputs / specs: B or S
[where B = Buy, S = Sell]

Put the labels "Today" and "Tomorrow" in E17:F17

------------
Put in G18
=IF(AND(E18="x",F18="x"),"Error",IF(E18="x",C18*D18*OFFSET($H$9,MATCH(A18&B1
8,$H$9:$H$12,0)-1,-3),IF(F18="x",-C18*D18*OFFSET($H$9,MATCH(A18&B18,$H$9:$H$
12,0)-1,-2),"")))

Copy G18 down to G20

Provided the additional inputs / specs are made in A18:B20,
G18:G20 will return the correct calculated result
based on the single* "x" in either E18:E20 or in F18:F20
[ i.e. as specified in your post ]


Error alerts provided in the IF formula in G18:G20

 

[Phoenix]

Sorry, Bob P, if I said anything wrong. The things is that i need to
build up a system, and I'm not sure what i'm looking for. Bikes/cars
and buy/sell is just a suggestion from my side on how to start to build
this system


Max: Thx for your answer That's what I call a really good answer!
And it seems i'm closer to a solution now


Phoenix

 

[Max]

Phoenix, you're welcome!
Thanks for the feedback.