Home

How do i do functions and formulars Please

%3CLINGO-SUB%20id%3D%22lingo-sub-717767%22%20slang%3D%22en-US%22%3EHow%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717767%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20do%20a%20task%20in%20a%20spreadsheet%2C%20and%20I%20am%20struggling%20with%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBudget%20is%20%C2%A310%2C000%20and%20the%20miles%20are%2020%2C000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20need%20to%20do%20the%20following%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdd%20a%20formula%20in%20column%20D%20such%20that%20it%20shows%20%E2%80%9CMight%20Buy%E2%80%9D%20if%20a%20car%20meets%20both%3CBR%20%2F%3Ecriteria%20and%20%E2%80%9CWon%E2%80%99t%20Buy%E2%80%9D%20if%20it%20does%20not.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAdd%20conditional%20format%20to%20column%20D%20so%20that%20%E2%80%9CMight%20Buy%E2%80%9D%20appears%20in%20green%20and%3CBR%20%2F%3E%E2%80%9CWon%E2%80%99t%20Buy%E2%80%9D%20appears%20in%20grey.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20totally%20lost%3B%20I%20am%20getting%20myself%20so%20mixed%20up%20that%20I%20wish%20I%20had%20never%20started%20it%20%F0%9F%98%8A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20very%20much%20appreciated%26nbsp%3B%F0%9F%98%8A%20%F0%9F%98%8A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-717767%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717813%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364959%22%20target%3D%22_blank%22%3E%40TiggerD%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20a%20formula%20along%20with%20two%20conditional%20formatting%20rules.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20I%20suggest%3A%3C%2FP%3E%3CPRE%3E%3DIF(AND(ISNUMBER(B2)%2CISNUMBER(C2))%2CIF(AND(B2%3D%24G%242%2CC2%3D%24G%243)%2C%22Might%20Buy%22%2C%22Won't%20Buy%22)%2C%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120461i06254FF017DA0049%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Nested%20IF.png%22%20title%3D%22Nested%20IF.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20regards%20to%20the%20Conditional%20Formatting%2C%20you%20need%20these%20two%20formulas%20in%20two%20separate%20rules%20and%20apply%20them%20to%20the%20column%20D.%3C%2FP%3E%3CPRE%3E%3DD2%3D%22Won't%20Buy%22%3C%2FPRE%3E%3CPRE%3E%3DD2%3D%22Might%20Buy%22%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20984px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120462i3E797001B3FC072B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Conditional%20Formatting%20Rules.png%22%20title%3D%22Conditional%20Formatting%20Rules.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20that%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718512%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364959%22%20target%3D%22_blank%22%3E%40TiggerD%3C%2FA%3E%26nbsp%3B%20Is%20this%20your%20homework%20assignment%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719363%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20it%20is%20a%20task%20I%20have%20been%20given%20to%20do%2C%20I%20need%20to%20learn%20this%20but%20just%20cant%20get%20my%20head%20around%20it%2C%20I%20have%20tried%20so%20many%20different%20ways%20%3A-(%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20even%20done%20the%20one%20above%20and%20it%20didn't%20work%20for%20me%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719364%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719364%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20replying%2C%20I%20have%20tried%20it%20and%20it%20didn't%20work%20for%20me%2C%20I%20am%20totally%20brain%20dead%20now%20as%20it%20works%20for%20other%20but%20not%20me%20%3A-(%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719936%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364959%22%20target%3D%22_blank%22%3E%40TiggerD%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat's%20wrong%20with%20it%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EPlease%20explain%20the%20problem%20you%20encountered!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EIt's%20best%20to%20give%20us%20a%20sample%20of%20the%20worksheet%20you're%20working%20on!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719980%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20what%20you%20had%20put%20and%20it%20was%20putting%20things%20in%20over%2020%2C000%20miles%2C%20I%20have%20tried%20several%20times%2C%20I%20guess%20it%20is%20something%20I%20am%20doing%20wrong%20%3A-(%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719992%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364959%22%20target%3D%22_blank%22%3E%40TiggerD%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk.%3C%2FP%3E%3CP%3ETry%20this%20formula%20instead%3A%3C%2FP%3E%3CPRE%3E%3DIF(AND(ISNUMBER(B6)%2CISNUMBER(C6))%2CIF(AND(B6%26lt%3B%3D%24B%241%2CC6%26lt%3B%3D%24B%242)%2C%22Might%20Buy%22%2C%22Won't%20Buy%22)%2C%22%22)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120595i594BB49EF41B6B98%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-06-25_21-56-49.png%22%20title%3D%222019-06-25_21-56-49.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722586%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20i%20do%20functions%20and%20formulars%20Please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%20for%20the%20help%20you%20have%20given%20me%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
TiggerD
Occasional Contributor

Hi everyone, 

 

I am trying to do a task in a spreadsheet, and I am struggling with it.

 

Budget is £10,000 and the miles are 20,000

 

So, I need to do the following 

 

Add a formula in column D such that it shows “Might Buy” if a car meets both
criteria and “Won’t Buy” if it does not.


Add conditional format to column D so that “Might Buy” appears in green and
“Won’t Buy” appears in grey.

 

I am totally lost; I am getting myself so mixed up that I wish I had never started it 😊

 

Any help will be very much appreciated 😊 😊

8 Replies

@TiggerD

 

Hi,

 

You need a formula along with two conditional formatting rules.

 

This is the formula I suggest:

=IF(AND(ISNUMBER(B2),ISNUMBER(C2)),IF(AND(B2=$G$2,C2=$G$3),"Might Buy","Won't Buy"),"")

 Nested IF.png

 

With regards to the Conditional Formatting, you need these two formulas in two separate rules and apply them to the column D.

=D2="Won't Buy"
=D2="Might Buy"

Conditional Formatting Rules.png

 

 

Please see that in the attached file.

 

Hope that helps

@TiggerD  Is this your homework assignment?

@Ingeborg Hawighorst 

 

No it is a task I have been given to do, I need to learn this but just cant get my head around it, I have tried so many different ways :-( 

 

I have even done the one above and it didn't work for me 

Hi 

 

Thank you for replying, I have tried it and it didn't work for me, I am totally brain dead now as it works for other but not me :-( 

@TiggerD

 

Hi,

 

What's wrong with it?

Please explain the problem you encountered!
It's best to give us a sample of the worksheet you're working on!

@Haytham Amairah 

 

Hi, 

I did what you had put and it was putting things in over 20,000 miles, I have tried several times, I guess it is something I am doing wrong :-( 

 

 

@TiggerD

 

Ok.

Try this formula instead:

=IF(AND(ISNUMBER(B6),ISNUMBER(C6)),IF(AND(B6<=$B$1,C6<=$B$2),"Might Buy","Won't Buy"),"")

2019-06-25_21-56-49.png

 

@Haytham Amairah Thank you so much for the help you have given me 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies