more than one IF

M

Micayla Bergen

i want my cell to return a value if J5 is 1 or 2 (which themselves are
results of formulas)
i have this thusfar
=IF(J6=1,"SELL?","",(IF(J6=2,"SELL?","")))
 
M

Micayla Bergen

IF(OR(U5=<'MDA Portfolio Summary'!H2,ACQUIRE,U5=>'MDA Portfolio Summary'!J2,
REDUCE}))
Thanks Max. now i want to have a cell return either Acquireif it is less
than H2 or Reduce if it is more than J2
 
M

Max

Try something like:

=IF(U5="","",IF(U5<='MDA Portfolio Summary'!H2,"ACQUIRE",IF(U5>='MDA
Portfolio Summary'!J2,"REDUCE","")))
 
M

Micayla Bergen

So =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4>'MDA
Portfolio Summary'!J8,"REDUCE",""))) was working, but now i get a REF error.
the values in H8 and J8 are percentages, and U4 is also a formula result as a
percentage. is this why i get a ref error, i cant find anything else wrong
Thanks again
 
M

Micayla Bergen

I have =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4>'MDA
Portfolio Summary'!J8,"REDUCE",""))) which should work but it actually
returns a REF error. i havent deleted anything so dont understand the error.
the answers in U4 and J and H8 are percentages. Is this the issue?
 
M

Max

Don't think the percentages in U4, J8 and H8 are an issue causing #REF!
Maybe one of the 3 precedent cells (U4, J8 and H8) is evaluating to #REF!
That's all it takes to cause the IF formula to, in turn, evaluate to #REF!
Try a re-check on the formulas in the 3 precedent cells (U4, J8 and H8)
 
M

Micayla Bergen

Hi Max
the values in H8 and J8 are just numbers as percentages, there are no
formulas in those cells. U4 is a formula but shows the result not a ref error
 
M

Max

Email over a copy of your file? I'll take a look.
Email to: demechanik <at>yahoo<dot>com
Let me know if you're sending or not here.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Micayla Bergen said:
Hi Max
the values in H8 and J8 are just numbers as percentages, there are no
formulas in those cells. U4 is a formula but shows the result not a ref
Click to expand...
error
 
M

Micayla Bergen

will send now, thanks Max

Max said:
Email over a copy of your file? I'll take a look.
Email to: demechanik <at>yahoo<dot>com
Let me know if you're sending or not here.
Click to expand...
 
M

Max

Aha ... there was a line break in the formula which was fouling up the
returns for " 'MDA Portfolio Summary'!J8 " and " 'MDA Portfolio Summary'!R8
" in both formula cells ! This was causing the #REF! errors

Example, for the formula in W4 in sheet: Template

: =IF(U4="","",IF(U4<'MDA Portfolio Summary'!H8,"ACQUIRE",IF(U4>'MDA
**Portfolio Summary'!J8,"REDUCE",""))

Just place the cursor at the asterisked point (**) in the formula, do a
back-space, then press a single space to correct .. so that it all reads
properly in a single line

Note that you'd normally need to rectify the inevitable line-breaks/wraps
present when you copy > paste formulas (especially long formulas) direct
from posts.

I've sent the corrected file to you.
 
C

CLR

Maybe something has happened to the reference source 'MDAPortfolio
Summary'!J8..........

Vaya con Dios,
Chuck, CABGx3
 
M

Max

CLR said:
Maybe something has happened to
the reference source 'MDA Portfolio Summary'!J8..........
Click to expand...

The source J8 was okay, but the formula was broken at that exact point
reference point by the dreaded line-breaks/wrapse <g>
 
M

Micayla Bergen

Thank you so much Max! i would not have thought that a line break in a
formula would make a difference!
 
M

Max

Thank you so much Max!
i would not have thought that a line break in a
formula would make a difference!
Click to expand...

You're welcome !
Yes, it's important to check / remove these line-break(s) after pasting the
posted formulas
 
C

CLR

Good job Max...........glad you got it fixed for him........

Vaya con Dios,
Chuck, CABGx3
 
M

Micayla Bergen

Max now the formulas on the 'mda model weighting' sheet are giving a ref
error. so i have deleted all the line breaks but the error remains. i am only
getting the error on the stocks that the clients have, not for every one, but
i cant find an error (or line break) on the individual sheets that the
formulas refer to
egad!
 
M

Max

This is actually a subject for another post(s) !
Let me take a closer look at the other formulas a little later.
I'll post back here.
 
M

Max

... now the formulas on the 'mda model weighting' sheet
are giving a ref error ...
Click to expand...

First, a clarification that the above actually had *nothing* to do with the
previous issue(s) raised and resolved earlier in this thread. I've cleaned
up and reconstructed the formulas in the problem sheet as per below
(Corrected file sent to you)

Clean-up and reconstruction of formulas
in sheet: MDA Model Weighting
------------------------------------------
The vlookup range was adjusted to: $A$4:$J$39 instead of $A$4:$I$39
The col index ref "10" in the vlookup for "Value" was causing the #REF!
everywhere previously because it pointed to a non-existent range ($A$4:$I$39
is only 9 cols!)

To facilitate copying down of formulas and to suit the sheet structure where
you had sheetnames merged and straddling 3 cols from cols D to AV, INDIRECT
was used. Along the way there were a couple of inconsistencies in the
sheetname structure for some sheets which were found and cleaned up.

Extraction of the 3 col index refs for the Vlookup per group of 3 cols was
automated via using another nested vlookup which read the 3 col headers
within each group: No. of Shares (4), Value (10), and Portfolio Weighting
(6)

Example: ... VLOOKUP(D$2,MDA_1,2,0) ...
where MDA_1 is a named range referring to a table in a new sheet: Index

No. of Shares 4
Value 10
Portfolio Weighting 6

Reconstructed formulas for cols D to AV
------------------------------------------------------
Typical set of 3 cols, e.g.: cols D to F

D1 contained the sheetname, e.g.: A & E John Super
(D1 was merged over D1:F1)

D2:F2 contained the 3 col headers:

No. of Shares
Value
Portfolio Weighting

Formula placed in D3:

=IF(ISNA(VLOOKUP($A3,INDIRECT("'"&$D$1&"'!$A$4:$J$39"),VLOOKUP(D$2,MDA_1,2,0
),0)),"",VLOOKUP($A3,INDIRECT("'"&$D$1&"'!$A$4:$J$39"),VLOOKUP(D$2,MDA_1,2,0
),0))

D3 was copied across 3 cols to F3

D3:F3 was then selected and copied for each successive set of 3 cols to the
right, to G3:I3, J3:L3, ... AT3:AV3 etc, and Edit > Replace used to replace
the $D$1 to $G$1, $J$1 ... etc

D3:AV3 could then be selected and copied down to AV47
 
Top