7 Replies Latest reply on Apr 18, 2013 6:55 AM by Ben Price

    IF function - Am I doing it wrong?

    Ben Price

      Hi there,

       

      I'm currently analysing a pretty large data set from a survey that we have been running for the last year.

       

      My trouble is that I can't seem to make a simple IF calculation work.

       

      I've attached a screenshot of our set-up - ALL questions are in one variable in columns and ALL answers are in another variable in rows.

       

      To get the attached, I've filtered by the question which then only shows the answers corresponding to that question.

       

      Our values are made up of a COUNTD of the unique Respondent ID (Arguably not the best way of doing it)

       

       

       

      Now what I'm trying to calculate is the average budget from this chart. - I started by trying the following IF function:

       

      IF [Answer]="£5,000.00" THEN COUNTD([RespondentID])*5000 END

       

      This would then give a number value to the total cost in each bracket - but it keeps returning the error "Cannot mix aggregate and non-aggregate comparisons or results in "IF" expressions".

       

      If I can get the above to work then that would be a start and I add some average functions to give me my total.

       

       

      There may also be a better way of structuring the data, so I've attached a snapshot of the excel that I'm working from too.

       

       

      Any thoughts or advice would be massively well received.

       

      Many Thanks,