Rhys

Right then, any one got an idea how i would right a forumla for this?

Drilling cost: for rock thickness below 5 metres $400,000
from 5 metres to under 9 metres $600,000
from 9 metres to under 11 metres $800,000
from 11 metres to under 14 metres $1,000,000
above 14 metres $1,200,000

Other costs (fixed) $3,000,000

Basically i want in Cell X to get me the drilling cost to what cell Y says the rock thickness is. Then once its worked that out also at the $3,000,000 onto it?

I know its an If formula but do you have to keep putting an If in for every Variable?

Dan B

Looks like you'll have to nest IF statements, but at least you'll be able to copy/paste the formula into every cell once it's written:

Pretty sure the above, converted to "normal English", means "If A1 is less than or equal to 5, then set value as 400000; else if A1 is than or equal to 9, set value to 600000, and so on...

Have that to work out the drilling cost, then use =SUM(B1+3000000) for the total cost.

The above assumes your rock thickness values are in the A-column and you're outputting drilling cost to the B-column; change those cell-addresses accordingly, paste the two formulae into the relevant columns!

It's been a while since I did any excel-formulae-programming, but I "think" that should work...

RSKim

WOW

Rhys

Awsome nice one Dan

Dan B

You're welcome!

Paul Eggleton

Check this badboy out

=(ALLOCATE!G302*VLOOKUP(ALLOCATE!H302,in!$BE$12:$B H$442,3))+(ALLOCATE!I302*VLOOKUP(ALLOCATE!J302,in! $BE$12:$BH$442,3))+(ALLOCATE!K302*VLOOKUP(ALLOCATE !L302,in!$BE$12:$BH$442,3))+(ALLOCATE!M302*VLOOKUP (ALLOCATE!N302,in!$BE$12:$BH$442,3))+(ALLOCATE!O30 2*VLOOKUP(ALLOCATE!P302,in!$BE$12:$BH$442,3))+(ALL OCATE!Q302*VLOOKUP(ALLOCATE!R302,in!$BE$12:$BH$442 ,3))

Tracing problems is, errrr, interesting

Rhys

LOL these formulas suck! lol

Vlookup another great function

For this one assingment i have been told i have to use the Poisson fct nd the Norminv fct Which i have both never heard of till the other day! I have now used them but not sure if they are correct

Paul Eggleton

Heard of Poissions ratio but wtf is Norminv? Normalised inverse something ???

Rhys

Its to do with Normal distribution. So yeah your right Normal inverse


Didnt know either of those fcts was in excel till the other day

foreigneRS

have you programmed your own functions using vba?

Rhys

I havent no.


Now lets not get to techinal

JamesH

iTrader: (21)

Soooooooo glad when I finished my Excel coursework

Rhys

Just doing this one now, but for some reason it aint working
=IF(C9=0,"0",IF(C9=1,"randbetween(800000,1000000)" ,IF(C9=2,"randbetween(800000,1000000)*2",IF(C9=3," randbetween(800000,1000000)*3",IF(C9=4,"Randbetwee n(800000,10000000*4")))))