Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 15 2021 09:36 AM - edited Jul 15 2021 09:44 AM

how do you write mutilple formulas for 1 cell. example (in one cell if the number is 5 then i want it to input in another cell the number 1 OR if the number in that same cell is 4 then i want it to input in another cell the number 2 and so on.

15 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 15 2021 10:13 PM

@Budwizer_10 A cell can only hold **one** formula, but it can have many different outcomes. On the other hand, multiple formulae can reference one particular cell.

If you could upload a schedule that gives a clear example of what you are dealing with, that would be helpful.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 16 2021 01:31 AM

Spreadsheets provide a functional, rather than imperative, programming style. That means they are specifically designed to prevent a value/cell being changed as a result of executing one or more instructions held elsewhere.

Rather than looking at the value in a cell and thinking "What do I want to happen as a result of this?", focus on the other cells and ask "How do I determine the value of this cell"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 16 2021 04:09 PM

Hi there, thanks so much for the response. Here is a copy of what i am trying to do.

This table is for a golf game and basically on hole 1 if par is a 4 and Greg scores a 6 he should get 0 points, if he scores a 5 he should get 1 point and he scores a 4 then 2 points and if he scores a 3 then gets 3 points. I am trying to input the mutiple what if formula into where the Points total (1) is and retreive the data from where Greg inputs his score (5)

Hole 1 Hole 2 Hole 3 .........etc

Par 4 5 3

Greg 5 4 4

Points 1

Not sure if this is possible but any further help would be very much appreciated.

This table is for a golf game and basically on hole 1 if par is a 4 and Greg scores a 6 he should get 0 points, if he scores a 5 he should get 1 point and he scores a 4 then 2 points and if he scores a 3 then gets 3 points. I am trying to input the mutiple what if formula into where the Points total (1) is and retreive the data from where Greg inputs his score (5)

Hole 1 Hole 2 Hole 3 .........etc

Par 4 5 3

Greg 5 4 4

Points 1

Not sure if this is possible but any further help would be very much appreciated.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 16 2021 04:10 PM

This table is for a golf game and basically on hole 1 if par is a 4 and Greg scores a 6 he should get 0 points, if he scores a 5 he should get 1 point and he scores a 4 then 2 points and if he scores a 3 then gets 3 points. I am trying to input the mutiple what if formula into where the Points total (1) is and retreive the data from where Greg inputs his score (5)

Hole 1 Hole 2 Hole 3 .........etc

Par 4 5 3

Greg 5 4 4

Points 1

Not sure if this is possible but any further help would be very much appreciated.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 16 2021 09:46 PM

@Budwizer_10 No problem. See attached workbook for a working example based on your description. But I suspect that your next question will be to include course rating, multiple players, stroke index, handicap and playing handicap.

In the attached file you'll find two solution. One with new Excel functions and one with an "old-style" approach.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 04:05 AM

I liked the 365 solution! Since you have the situation under control, I set out to play with Lambda functions. I think it may be a while before I really get settled into the new ways of working and read the formulas fluently; they still don't look like spreadsheets to me.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 07:41 AM

Riny/van/Eekelen, thank you so much this is what i was looking for and it now makes sense, what you are saying. Excel is unable to create a mutiple formula in one cell so you have to create a data table to draw from for all the what ifs. Again thank you so much. Greg

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 07:43 AM

Peter, I want to thank you also this gives me a better understanding of what i was looking for and it now makes sense, what you are saying. Excel is unable to create a mutiple formula in one cell so you have to create a data table to draw from for all the what ifs. Again thank you so much. Greg

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 08:35 AM

One last question, Your spreadsheet works perfect but when I try to copy paste the scores and points values for all of 1 player to create another player the formulas do not work any longer. Am i missing something in the copying and pasting?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 08:37 AM

@Budwizer_10 Who's spreadsheet are you referring to? Easier if you upload your schedule so that either @Peter Bartholomew or I can help you out.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 08:42 AM

Hi there, either yours or Peters i could not seem to copy paste to create another player. I am not sure how to upload a copy of mine otherwise i would. Where do i upload a copy. thx

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 08:46 AM

best response confirmed by
Budwizer_10* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17 2021 08:59 AM

Solution@Budwizer_10 OK, you went for a horizontal lookup table. Not sure if you can work with the newer functions so I added the "old-style" formulae, after changing the lookup table a bit. See attached.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 19 2021 06:56 AM

Riny_van_Eekelen, I truly want to thank for your help, it works great and you have given me a better understanding of excel.