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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies