Google Spreadsheet If and Search functions with Arrayformula

Question:

( by Violet )

I'm new to Google Docs and have recently created a Google Form with responses contained in a Google Sheet.  I want the sheet to calculate values but the cells only contain text and this has to remain for the end user.  Is is possible to calculate the following in cell W2:

IF H2 contains STANDARD then 520, IF I2 contains UPGRADE then 590, IF K2 contains UPGRADE then 640

Any help greatly appreciated. 

=====================================

Me:

Hi Violet,

try the following formula in Cell W2:

=if(H2="STANDARD";520;if(H2="UPGRADE";590;if(H2="UPGRADE";640;)))

If you provide more details then we can help you more, like you can use Arrayformula to compute this for whole column W with a single formula...

And what if both Cells H2 contains "STANDARD" and I2 contains "UPGRADE" then ??

It will help if you share your Google spreadsheet (not a screenshot, not an Excel file) with say 5 or more rows of sample but realistic data, and 

    a) tell us what you need help with

    b) in which cell?

        of which sheet?

    c) show us your expected result

        along with needed logic/explanation

I hope this helps you,

===============================

Violet:

H

i Kishan

Thanks for your prompt reply.  I have tried the formula you provided but if does not work - I probably didn't explain what I required correctly!

To answer your query cells H2 and I2 can not both contain data as the spreadsheet is based on a form with multiple choice answers but 

I have re looked at the sheet and can now query a single column:

If cell G2 (G3, G4 etc) contains:

Standard then 520

Option 1 then 590

Option 2 then 640

Option 3 then 685

Option 4 then 700

Option 5 then 750   

I have input some data and have a spreadsheet to share.  Do I just share this through Google Drive or send as an attachment?

Many thanks

=================

Violet:

This sheet records responses to a form for customer carpet selection. I want column Y to calculate the total due from the customer to enable us to match to an invoice at a glance. I found an iferror formula on the forum and adpated it but it only provide for one argument

Solution:

Have a look at the screenshot of the spreadsheet:

Now put the following formula in Cell W1:

=arrayformula(if(row(G:G)=1;"Total due from purchaser";if(G:G="";"";(iferror(if(search("standard";G:G);520);iferror(if(search("option 1";G:G);590);iferror(if(search("option 2";G:G);640);iferror(if(search("option 3";G:G);685);iferror(if(search("option 4";G:G);700);iferror(if(search("option 5";G:G);750);)))))))+(if(Q:Q="YES";250;0)))))


Now if Column G contains "standard" then Column W will add as 520 to its value and it Column Q has "Yes" then Column W will add 250 more to its value and will have 770 (520+250).

Similarly if Column G contains "option 1" then Column W will add as 590 to its value and it Column Q has "Yes" then Column W will add 250 more to its value and will have 840 (590+250).

And so on...

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive