• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

I need help with Excel function interpretations

Hi All,
      I need help with Excel function interpretations:

      This is my interpretation of this one:            =IF(AND(OR(ISBLANK(K8),K8<>0),J8=0,B8=0),K8,0)      
      if (ISBLANK(K8) OR K8<>0) AND J8=0 ???? then
         K8
      else
         0
      end if;
      
      This is my interpretation of this one:      =IF(W60=0,IF(OR(P60<0,O60<0),P60,0),0)
      if W60=0 then
        IF(P60<0 OR O60<0)then
           P60
        else
          0
        end if
      else
        0
      end if

      =SUMIF('Various Adj'!$F$5:$F$500,A134,'Various Adj'!$K$5:$K$500)
      =IF(AND(D134<>0,J134=0),P134,0)
      =SUMIFS(FGI!$L$2:$L$20000,FGI!$A$2:$A$20000,A134,FGI!$G$2:$G$20000,"FGI")
      =ROUND(SUMIF('SO Backlog'!$D$2:$D$7947,A134,'SO Backlog'!$K$2:$K$7947),0)
      =IF(ISERROR(+H134/B134),0,+H134/B134)
      =SUMIFS(Rev!$V$2:$V$3000,Rev!$AO$2:$AO$3000,A8,Rev!$W$2:$W$3000,"REV")

Thank you for your help,
Bill
0
bcarlis
Asked:
bcarlis
  • 5
  • 3
5 Solutions
 
Phillip BurtonCommented:
=IF(AND(OR(ISBLANK(K8),K8<>0),J8=0,B8=0),K8,0)  is
      if (ISBLANK(K8) OR K8<>0) AND J8=0 AND B8 = 0
then
         K8
      else
         0
      end if;
0
 
Phillip BurtonCommented:
=SUMIF('Various Adj'!$F$5:$F$500,A134,'Various Adj'!$K$5:$K$500)

For each cell in 'Various Adj'!$F$5:$F$500
If it is equal to A134
then add the corresponding cell in column K.
0
 
Phillip BurtonCommented:
=SUMIFS(FGI!$L$2:$L$20000,FGI!$A$2:$A$20000,A134,FGI!$G$2:$G$20000,"FGI")
Sum column L (between rows 2 and 20000) IF, and ONLY IF
   the corresponding row in column A equals A134
   AND
   the corresponding row in column G equals "FGI"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Phillip BurtonCommented:
Surely you can work out the rest yourself, as they are the same as the above.
0
 
bcarlisAuthor Commented:
=SUMIF('Various Adj'!$F$5:$F$500,A134,'Various Adj'!$K$5:$K$500)
For each cell in 'Various Adj'!$F$5:$F$500
If it is equal to A134
then add the corresponding cell in column K.

Open in new window


"Various Adj" is another tab
so "For each cell in Various Adj'!$F$5:$F$500"

so how do I break this apart?          
!$F$5:$F$500
0
 
Phillip BurtonCommented:
For each cell in the spreadsheet Various Adj, cells $F$5:$F$500
   If it is equal to the current spreadsheet, cell A134
then add the corresponding cell in the spreadsheet Various Adj, column K.
0
 
bcarlisAuthor Commented:
Great.. I'm seeing it.. will get back to you..

thank you!
Bill
0
 
Rob HensonIT & Database AssistantCommented:
Couple that might not be covered by Phillip's explanations:

 =ROUND(SUMIF('SO Backlog'!$D$2:$D$7947,A134,'SO Backlog'!$K$2:$K$7947),0)
SUMIF is explained above; this will ROUND the result of the SUMIF to zero decimals, syntax is:
=ROUND(Formula,Decimals)

=IF(ISERROR(+H134/B134),0,+H134/B134)
This is looking at the formula "H134 divided by B134", if the result is an error then put zero else put result. Depending on version of excel (2007 or later) you can use the following:

=IFERROR(H134/B134,0)
Syntax is:
=IFERROR(Formula, Error_Result)

Thanks
Rob H
0
 
bcarlisAuthor Commented:
Thank you both for your help!
May need some more but will open new question.
Thx again, Bill
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now