Greetings,
You can do this with a set of VLOOKUP() functions. I started by
creating a table of values like so:
Ref Value B C D E F G H I 1 6 10 16 28 38 46 60 80 2 12 20 32 56 76 92
120 160 3
180 40 4
30
The values in column 1 are the values you will find in the B to I
cells. The other values are the values you will want to get based on
the IF() statements. I also named that table RefValues. Now, instead of
using something like:
=IF(B4=1;6)+IF(B4=2;12)+IF(C4=1;10)+IF(C4=2;20)
you can now use something like this:
=IFERROR(VLOOKUP(B4,RefValues,2,0),0)+IFERROR(VLOOKUP(C4,RefValues,3,0)
,0)+(...)+IFERROR(VLOOKUP(i4,RefValues,9,0),0)
If you really want to be fancy, you can do this with an array formula
that will process the entire set in one go, like so:
{=SUM(IFERROR(VLOOKUP(B4:I4;RefValues;COLUMN(B4:I4);0);0))}
To get this in, you enter the formula without the braces and then
terminate with CTRL-SHIFT-Enter. You may need to replace the
COLUMN(B4:I4) with something different if you want to index correctly
in the RefValue table.
I hope this helps.
Rémy.
Le mardi 11 octobre 2022 à 06:59 -0500, Wade Smart a écrit :
> The reason is as the IF statements have grown, changed, copied --
> they
> are not working as they should. And this is why Im looking for a
> better way. IF!!! I could do this in a db with a better front end -
> that would be great but - Im not sure this company will let me do
> that
> so for right now - small steps to at least fix all the problems this
> sheet is causing. As for specifically H and I, both of those items
> have had - as said in the original post - more possible values added
> to over time, and will each have 5 possible values in a few months
> so,
> the IF statement will be updated again and if someone put the wrong
> numbers in or it gets corrupted by the copying - Ill need to go back
> and fix that. Its a mess.
>
>
> --
> Registered Linux User: #480675
> Registered Linux Machine: #408606
> Linux since June 2005
> On Mon, Oct 10, 2022 at 8:22 PM Michael D. Setzer II
> <msetze...@gmail.com> wrote:
> >
> > Looked at the spreadsheet and tried some things, but not clear on
> > your calculation.
> > Copied formula from first row down to row 16 then modified.
> > =IF(B16=1,6)+IF(B16=2,12)
> > +IF(C16=1,10)+IF(C16=2,20)
> > +IF(D16=1,16)+IF(D16=2,32)
> > +IF(E16=1,28)+IF(E16=2,56)
> > +IF(F16=1,38)+IF(F16=2,76)
> > +IF(G16=1,46)+IF(G16=2,92)
> > +IF(H16=1,60)+IF(H16=2,120)+IF(H16=3,180)+IF(H16=4,30)
> > +IF(I16=1,80)+IF(I16=2,160)+IF(I16=3,40)
> > Not clear why H16 being 4 give 30 where previous patterns would
> > have it give 240?? Same with I16 being 3 gives 40 instead of 240.
> > Came with this simple formula and it mached result in most cases.
> > =b16*6+c16*10+d16*16+e16*28+f16*38+g16*46*h16*60+i16*80
> > Copied the relative formulas and numbers matched except where
> > values in H or I was 4 or 3.
> > Didn't look at vlookup formula.
> > Did note that on some of the lower rows you have blanks in cells
> > that resulted in #value, but chaning them to 0 resulted in correct
> > numbers.
> > So, is there a reason why H and I being 4 or 3 give different
> > values from the pattern. Same thing with shifting to other blocks.
> > On 10 Oct 2022 at 18:40, Wade Smart wrote:
> > From: Wade Smart <wadesm...@gmail.com>
> > Date sent: Mon, 10 Oct 2022 18:40:55 -0500
> > Subject: Re: [libreoffice-users] [more] Vlookup, and other
> > Help
> > Copies to: users@global.libreoffice.org
> > > Well, that is odd.
> > > Im uploading again ...
> > >
> > > What about this link?
> > > http://drive.google.com/file/d/1-9vnC9UAoffLdXcVDTTN6oIJ8UJO041w/view?usp=sharing
> > > --
> > > Registered Linux User: #480675
> > > Registered Linux Machine: #408606
> > > Linux since June 2005
> > >
> > > On Mon, Oct 10, 2022 at 6:37 PM Steve Edmonds
> > > <steve.edmo...@ptglobal.com> wrote:
> > > >
> > > > At that link I get no download option and a message that the
> > > > file is in
> > > > your trash.
> > > > Steve
> > > >
> > > > On 11/10/2022 12:27, Wade Smart wrote:
> > > > > http://drive.google.com/file/d/1Q4Skv003T-puY-2CBr-MMQHUVC-qBhfw/view?usp=sharing
> > > > >
> > > > > I thought I was making headway but just got myself into more
> > > > > of a problem.
> > > > >
> > > > > The link above is a sample of what Im working with.
> > > > > In this spread sheet I have hundreds of sections of this that
> > > > > are
> > > > > added to and updated.
> > > > >
> > > > > If you click on cell BG4 you'll see the IF hell function that
> > > > > I
> > > > > continually update (and fix).
> > > > > This is created by someone else and the IF statement doesnt
> > > > > account
> > > > > for all the things
> > > > > that are currently going on but - in general - Section One is
> > > > > B4 to
> > > > > I4. The entire colored
> > > > > space from B3 to I13 is the whole section. Each row is
> > > > > calculated by
> > > > > putting in 1 thru 4 under
> > > > > each item name, though at the moment, and the reason Im
> > > > > looking to do
> > > > > this [upgrade] is that
> > > > > 3 and 4 and the coming 5 are not being calculated.
> > > > >
> > > > > Row 4, B4 to I4 either a 1 or 2 is put in. J4 is the number
> > > > > of items.
> > > > > Cell BE4 totals all items from each section.
> > > > >
> > > > > Cell BG4 - currently the massive and incorrect IF statement -
> > > > > adds up
> > > > > each cell and calculates
> > > > > a number. In this example, F4 is BL SM and is a 1 so BG is
> > > > > 38. Each
> > > > > row can have a 1 thru 4
> > > > > (upcoming 5) in each of the available cells.
> > > > >
> > > > >
> > > > > Looking at Row 7, Cell BG7 is the start of something I
> > > > > thought would
> > > > > work but... I got stuck.
> > > > > And, to be really honest, I got stuck writing this email for
> > > > > help
> > > > > because - I got lost in trying to
> > > > > explain this HAHA
> > > > >
> > > > > Looking at the function in BG7, check if its a number first
> > > > > and then
> > > > > look up the value of F3 item
> > > > > 1 in Sheet two which is 38. Cell BG8 does the same but shows
> > > > > the
> > > > > quickly growing IF statement
> > > > > for getting data for 2 cells.
> > > > >
> > > > > Question: does someone know of an easier way to do this
> > > > > without
> > > > > ongoing IF statements?
> > > > >
> > > > > Wade
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > To unsubscribe e-mail to:
> > > > users+unsubscr...@global.libreoffice.org
> > > > Problems?
> > > > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > > Posting guidelines + more:
> > > > http://wiki.documentfoundation.org/Netiquette
> > > > List archive:
> > > > http://listarchives.libreoffice.org/global/users/
> > > > Privacy Policy: http://www.documentfoundation.org/privacy
> > >
> > > --
> > > To unsubscribe e-mail to:
> > > users+unsubscr...@global.libreoffice.org
> > > Problems?
> > > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more:
> > > http://wiki.documentfoundation.org/Netiquette
> > > List archive: http://listarchives.libreoffice.org/global/users/
> > > Privacy Policy: http://www.documentfoundation.org/privacy
> > +------------------------------------------------------------+
> > Michael D. Setzer II - Computer Science Instructor (Retired)
> > mailto:mi...@guam.net
> > mailto:msetze...@gmail.com
> > Guam - Where America's Day Begins
> > G4L Disk Imaging Project maintainer
> > http://sourceforge.net/projects/g4l/
> > +------------------------------------------------------------+
>
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
Privacy Policy: http://www.documentfoundation.org/privacy
