help with a nested Function

[Guest]

Hi
I can across the following function in a worksheet, in an attempt to understand the formulae, i am attempting to replicate the data and formulaes. Please could you advise as to why formulae returns a #REF error

=IF($C$3="",0,ROUND((1+(VLOOKUP($C$3,bpdat,3,FALSE)/(1+$L4)))*VLOOKUP($C$3,bpdat,8,FALSE)*(1.25-(0.05*$C$22)),0))

where $C$3 is a dropdown list (on a different Sheet)
$l4 is a client entered value between 1-5
Column labels
Name Catergory Waste Factor Build Time R.Min Time R.Copy Time R.Prod Time Tritanium Pyerite Mexallon Isogen Nocxium Zydrine Megacyte Max Runs
1st item
Gamma L Ammo Crystals 0.1 120 2400 2400 2400 0 46 2 3 18 6 40 1500

$c$22 is again a client entered value between 1-5
Bpdat is a collection of relevant data(see below for example)

 

[JulieD]

Hi steve
different sheet from where the IF formula is being calculated from? if so,
when you're referencing a cell on a different sheet you need to include the
sheet name,
e.g. =IF(Sheet1!$C$3=

also i think you're bracketing isn't exactly right .. if i understand what
you're after correctly i would go with the following.

=IF($C$3="",0,ROUND(((1+VLOOKUP($C$3,bpdat,3,FALSE)/(1+$L4))*(VLOOKUP($C$3,b
pdat,8,FALSE))*(1.25-0.05*$C$22)),0))

let us know how you get on

Cheers
JulieD

Hi
I can across the following function in a worksheet, in an attempt to

understand the formulae, i am attempting to replicate the data and
formulaes. Please could you advise as to why formulae returns a #REF error

=IF($C$3="",0,ROUND((1+(VLOOKUP($C$3,bpdat,3,FALSE)/(1+$L4)))*VLOOKUP($C$3,b
pdat,8,FALSE)*(1.25-(0.05*$C$22)),0))

where $C$3 is a dropdown list (on a different Sheet)
$l4 is a client entered value between 1-5
Column labels
Name Catergory Waste Factor Build Time R.Min Time R.Copy Time R.Prod Time

Tritanium Pyerite Mexallon Isogen Nocxium Zydrine Megacyte Max Runs

 

[Guest]

Hi Julie
Thanks for your answer, un fortunately it didnt resove the problem
I will try and explain in more detail
in this spreadshhet there are 2 pages
Page1 = BP Data i list of items with corresponding data for each item
page2 = Production calculator

prod Calc has a drop down list in it of all the items listed in Bpdata, the validation data for it is as follows:
=OFFSET(bpnames,2,0,0,400)
Cell reference for the LIst is C3(absolute $C$3)

Following that is a table that charts each mineral required to build a particular item(items from the list of bpdata)

Formulae in each respective mineral collumn is as follows.
=IF($C$3="",0,ROUND((1+(VLOOKUP($C$3,bpdat,3,FALSE)/(1+$L4)))*VLOOKUP($C$3,bpdat,8,FALSE)*(1.25-(0.05*$C$22)),0))
where $L4 is a client imput value
and $C$22 is also client imput

When trying to replicate the above ona new spread sheet, formulae = #REf.
I hope this is a little clearer than my last post

 

[JulieD]

Hi Steve

when you choose insert / name / define and look at bpdat does the sheet name
appear next to it in the list of range names?
when you click on it, what does it "refer" to.

also what does bpnames refer to in the same dialog box.

Cheers
JulieD

Hi Julie
Thanks for your answer, un fortunately it didnt resove the problem
I will try and explain in more detail
in this spreadshhet there are 2 pages
Page1 = BP Data i list of items with corresponding data for each item
page2 = Production calculator

prod Calc has a drop down list in it of all the items listed in Bpdata,

the validation data for it is as follows:

=OFFSET(bpnames,2,0,0,400)
Cell reference for the LIst is C3(absolute $C$3)

Following that is a table that charts each mineral required to build a

particular item(items from the list of bpdata)

 

[Guest]

Hi Julie

Yes when checking the bpdat reference bpdat refers to
=BPDATA!$A$11:$P$397

bpnames refers to the following range
=BPDATA!$B$14:$B$447

 

[JulieD]

Hi steve

okay ... not sure but i think there might be something wrong with your
OFFSET function ... the first parameter (AFAIK) must be a single cell
reference .. if you change it to reference BPDATA!$B$14 does that make a
difference?

Cheers
JulieD

 

[Guest]

Hi Julie
Just wanted to say thank you very much for the time, and effort that you have put into this, I seem to have figured most of this out, and have the formulae working (albeit without the Offset part).
Personally i can see no reason as to why Offset was used in the first pace, as it works perfectly without it

Thanks again for the direction which led to the answers
Steve